excel - 调试代码以将选项卡从一个工作簿移动到另一个作为循环的一部分从主管打开的工作簿

Question

所以我有一个工作簿，我试图将多个选项卡带入作为循环的一部分打开的工作簿中，但我不断收到错误代码“运行时错误 9 下标超出范围”并且无法弄清楚原因：

到目前为止，这是我的代码：

子前滚()

Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False


Set wb1 = Workbooks.Open("C:\Users\Desktop\700 Test\700 code template") 'move to shared file location when finished
Set wb2 = Workbooks.Open("C:\Users\Documents\Rollforward\2020 TB.xlsx")
Set wb3 = Workbooks.Open("C:\Users\Documents\Rollforward\2019 TB.xlsx")


    Dim wb4 As Workbook    


Application.ScreenUpdating = False




Set MyFile = Application.FileDialog(msoFileDialogFolderPicker)

    With MyFile

    .Title = "Please select folder"

    .Show

    .AllowMultiSelect = False

    If .SelectedItems.Count = 0 Then 

    MsgBox "You did not select a folder."

    Exit Sub

    End If

    MyFolder = .SelectedItems(1) & "\" 'Assign the selected folder to MyFolder (defines it)

End With

MyFile = Dir(MyFolder) 


Do While MyFile <> ""


    Set wb4 = Workbooks.Open(Filename:=MyFolder & MyFile)

wb4.Activate

Application.ScreenUpdating = False
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Sheets("P&L").Delete
Sheets("B-S").Delete


wb1.Sheets("Cover").Range("A9:B20").Copy

wb4.Sheets("Cover").Range("A9").PasteSpecial


wb2.Activate

Dim arr As Variant

arr = wb4.Sheets("March 2020 TB").Range("A1").Value

Worksheets("Report1").Range("A3").Autofilter _
 Field:=1, _
 Criteria1:=arr, _
 VisibleDropDown:=False



Dim startCell As Range, LastRow As Long, LastCol As Long, ws As Worksheet

Set ws = Sheets("Report1")
Set startCell = Range("A4")

    LastRow = ws.Cells(ws.Rows.Count, startCell.Column).End(xlUp).Row
    LastCol = ws.Cells(startCell.Row, ws.Columns.Count).End(xlToLeft).Column

ws.Range(startCell, ws.Cells(LastRow, LastCol)).Select
Selection.Copy
wb4.Activate
Sheets("March 2020 TB").Select
Range("B3").PasteSpecial Paste:=xlPasteValues

wb3.Activate



Worksheets("Report1").Range("A3").Autofilter _
 Field:=1, _
 Criteria1:=arr, _
 VisibleDropDown:=False


Set ws = Sheets("Report1")
Set startCell = Range("A4")

    LastRow = ws.Cells(ws.Rows.Count, startCell.Column).End(xlUp).Row
    LastCol = ws.Cells(startCell.Row, ws.Columns.Count).End(xlToLeft).Column

ws.Range(startCell, ws.Cells(LastRow, LastCol)).Select
Selection.Copy
wb4.Activate
Sheets("March 2019 TB").Select
Range("B3").PasteSpecial Paste:=xlPasteValues




wb1.Activate 'Template file

Worksheets("P&L").Activate
    ActiveSheet.Copy After:=wb4.Sheets(6)

wb1.Activate

Worksheets("B-S").Activate
    ActiveSheet.Copy After:=wb4.Sheets(7)


wb4.Activate
Sheets("Cover").Activate

Range("B9").Formula = "=VLOOKUP(""Net Assets"",'B-S'!A6:E37,5,0)*1000"



wb1.Activate

Worksheets("P1 - Trade creditors").Activate
    ActiveSheet.Copy After:=Workbooks("wb4").Sheets(13)

最后一行是我得到错误的地方。任何帮助都会很棒！

标签： excelvbaloopsdebugging