VBA excel打开文件和复制工作表错误下标超出范围

Question

我正试图从另一个excel文件复制工作表到其中一个工作表，但我得到了一个下标超出范围的错误。此错误来自行'.worksheet(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

Answer 1

下面试试

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