excel - 运行时错误“438”对象不支持此属性或方法

Question

我尝试运行我的代码，但是当我尝试将变量存储在 sht1 和 sht2 中以获取基于索引的工作表时出现运行时错误

我试图将数组“lasSheets(i) 和 dataPull(i)”设置为 sht1 和 sht2，这样它就可以选择索引 i 处的数组值，但我得到一个“编译器错误：预期的子、函数或属性”所以我跑了不知道下一步该做什么

 Private Sub CommandButton1_Click()
    Dim source As Workbook
    Dim sht1 As Worksheet
    Dim destination As Workbook
    Dim sht2 As Worksheet
    Dim tmp As String
    Dim startCell As Range
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim i As Integer
    Dim mapDest As String
    Dim mapSrc As String

    setFile = Application.GetOpenFilename   'used to open the browser window
    tmp = setFile                           'store the selected file in variable tmp
    Application.ScreenUpdating = False      'preventing long runtimes

    If Dir(tmp) <> "" Then
    Set destination = ThisWorkbook     'workbook b1 is declared as the current worksheet opened
    Set source = Workbooks.Open(tmp)      'the file the user selected is opened in excel

    Dim lasSheets(1 To 9) As String        'array to list the sheet names

    lasSheets(1) = "L1 OVERVIEW"
    lasSheets(2) = "LAS EFFL RELEASE PARAMS"
    lasSheets(3) = "L1 EAL PARAMS rev4"
    lasSheets(4) = "L1 EAL PARAMS"
    lasSheets(5) = "L1 RAD STATUS"
    lasSheets(6) = "L1 PLANT STATUS"
    lasSheets(7) = "L1 CDAM"
    lasSheets(8) = "L1 ERDS"
    lasSheets(9) = "LAS STATE UPDATES"

    Dim dataPull(1 To 9) As String
    dataPull(1) = "Overview Paste"
    dataPull(2) = "Eff Release Para Paste"
    dataPull(3) = "EAL Rev4 Paste"
    dataPull(4) = "EAL Para Paste"
    dataPull(5) = "Radiological Stat Paste"
    dataPull(6) = "Plant Status Paste"
    dataPull(7) = "CDAM Paste"
    dataPull(8) = "ERDS Paste"  ' blank sheet
    dataPull(9) = "State Updates Paste"   'blank sheet

    For i = 1 To 9
      mapSrc = lasSheets(i)
      mapDest = dataPull(i)
    i = i + 1
     Set sht1 = source.Sheet(mapSrc)            '<-- errors here set sht1 and sht2 to the source and destination worksheets   
     Set sht2 = destination.Sheet(mapDest)  

    Set startCell = sht1.Range("B2")

    'find last row and last column
     lastRow = sht1.Cells(sht1.Rows.Count, startCell.Column).End(xlUp).Row
     lastColumn = sht1.Cells(startCell.Row, sht1.Columns.Count).End(xlToLeft).Column

     sht2.Range(startCell, sht1.Cells(lastRow, lastColumn).Address).Copy destination:=sht1.Range("D5")

    Application.CutCopyMode = False
    Next i
    destination.Close True

    Else                                'used to prevent a error message from popping up when the user choose to cancel selecting a file

    End If
    End Sub

我希望for循环遍历两个数组并根据两个数组中的索引号输出excel工作表

标签： excelvba