VBA excel open file and copy worksheet error subscript out of range

Question

I am trying to copy a sheet from another excel file to one of the sheets but I got an subscript out of range error. The error comes from this line, ' .Worksheets(1).UsedRange.Copy ThisWorkbook.Worksheets("Sheet1").Range("A1")'.

Dim FileToOpen As Variant
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Excel File to Open", _
FileFilter:="Excel Files *.xlsx(*.xlsx),")
If VarType(FileToOpen) = vbBoolean Then
   MsgBox "No file selected", vbExclamation, "Sorry!"
    Exit Sub
End If
Debug.Assert VarType(FileToOpen) = vbString
With Application.Workbooks.Open(FileToOpen)
  .Worksheets(1).UsedRange.Copy ThisWorkbook.Worksheets("Sheet1").Range("A1")
  .Close
End With

Tomasz Tomasz · Accepted Answer · 2021-02-18T07:10:09

try below

Dim FileToOpen As Variant
dim tempFile as workbook
dim tempWs as worksheet
dim thisWs as worksheet

Application.ScreenUpdating = False
set thisWs = ThisWorkbook.Worksheets("Sheet1")

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Excel File to Open", _
FileFilter:="Excel Files *.xlsx(*.xlsx),")
If VarType(FileToOpen) = vbBoolean Then
   MsgBox "No file selected", vbExclamation, "Sorry!"
   Exit Sub
End If
Debug.Assert VarType(FileToOpen) = vbString
set tempFile =Workbooks.Open(FileToOpen)
set tempWs  = tempFile.worksheets(1)

tempWs  .cells.Copy thisWs.Range("A1")
tempFile .close 

set tempFile = nothing
set tempWs  = nothing

Application.ScreenUpdating = true

VBA excel open file and copy worksheet error subscript out of range

1 Answers