subscript out of Range (Run time error 9), need help

Hi,

AM having an error "subscript out of Range (Run time error 9)" in this macro

Sub ss()
FileToOpen = Application.GetOpenFilename _
(Title:="Please Choose the RTCM File", _
FileFilter:="Excel Files *.xls (*.xls),")

If FileToOpen = False Then
    MsgBox "No file specified.", vbExclamation, "Duh!!!" ' Notification that nothing is chosen
    Exit Sub
Else ' Load the file, copy the first sheet and paste it in active sheet ...
    ThisWorkbook.Activate
    ThisWorkbook.ActiveSheet.Range("A1:Z65536").ClearContents
    Workbooks(FileToOpen).Activate
    lrow = Workbooks(FileToOpen).Sheets("Sheet1").Cells(65536, 1).End(xlUp).Row
    Workbooks(FileToOpen).Sheets("Sheet1").Range("A1:Z" & lrow).Copy
    ThisWorkbook.Activate
    ThisWorkbook.ActiveSheet.Range("A1").PasteSpecial xlPasteValues
End If
End Sub

I highlighted a line, where i get this error.

Please help

Thanks

Try this

Sub ss()
Dim filetoopen As Variant

filetoopen = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", Title:="Please open the CSV file")
If filetoopen = "False" Then
    MsgBox "No file specified.", vbExclamation, "Duh!!!" ' Notification that nothing is chosen
    Exit Sub
Else
    ' Load the file, copy the first sheet and paste it in active sheet ...
    ThisWorkbook.Activate
    ThisWorkbook.ActiveSheet.Range("A1:Z65536").ClearContents
    Workbooks(filetoopen).Activate
    lrow = Workbooks(filetoopen).Sheets("Sheet1").Cells(65536, 1).End(xlUp).Row
    Workbooks(filetoopen).Sheets("Sheet1").Range("A1:Z" & lrow).Copy
    ThisWorkbook.Activate
    ThisWorkbook.ActiveSheet.Range("A1").PasteSpecial xlPasteValues
End If


End Sub

Hi, shiva_reshs,

GetOpenFilename gives you the path and the name of the file to open but it doesn´t open the file. So you would to do that prior to activating.

Instead of

Workbooks(filetoopen).Activate

you should rather use

Workbooks.Open (FileToOpen)

Ciao,
Holger

Try this.

Sub ss()
Dim wbSrc As Workbook

FileToOpen = Application.GetOpenFilename _
(Title:="Please Choose the RTCM File", _
FileFilter:="Excel Files *.xls (*.xls),")

If FileToOpen = False Then
    MsgBox "No file specified.", vbExclamation, "Duh!!!" ' Notification that nothing is chosen
    Exit Sub
Else ' Load the file, copy the first sheet and paste it in active sheet ...
   
    ThisWorkbook.ActiveSheet.Range("A1:Z65536").ClearContents
    Set wbSrc = Workbooks(FileToOpen)    
    With wbSrc
       lrow = .Sheets("Sheet1").Cells(65536, 1).End(xlUp).Row
      .Sheets("Sheet1").Range("A1:Z" & lrow).Copy
      ThisWorkbook.ActiveSheet.Range("A1").PasteSpecial xlPasteValues
      .Close False
     End With
End If
End Sub

Simple - change Sheet1 in this line to Sheet3 -

 .Sheets("Sheet1").Range("A1:Z" & lrow).Copy