Debug code to move tabs from one workbook to another that is opened from a director as part of a loop

Question

So i have a workbook i am trying to bring into a number of tabs into a workbook opened as part of a loop but i keep getting the error code "Runtime error 9 subscript out of range" and can't figure out why:

Here is my code so far:

Sub Rollforward()

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)

The last line is where i get the error. Any help would be great!!

```wb4.sheets(13)```. ```Workbooks()``` requires the actual file name. — Warcupine, May 22 '20 at 19:33
Might want to give [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) a read also. — Warcupine, May 22 '20 at 19:34
Ah okay, I will give that link a read! So its not possible to perform that action as part of the file that is currently open in the loop? — Bobby Watson, May 23 '20 at 09:32
@BobbyWatson It is possible, just your syntax is wrong. You've written `Workbooks("wb4")` which is looking for a workbook named "wb4". As Warcupine and Tim's comments suggest, use your variable representing the workbook, rather than the `Workbooks` collection. — Samuel Everson, May 23 '20 at 13:10

Debug code to move tabs from one workbook to another that is opened from a director as part of a loop

0 Answers0