VBA loop through workbooks + copy&paste

Question

I would like to take data from different workbooks (which have the same structure) and create a loop to copy&paste the data retrieved in the active workbook. I tried this but it doesn't work.

Sub loopMacro()
Dim Wb1 As Workbook, Wb2 As Workbook, Wb3 As Workbook
Dim MainBook As Workbook

'Open all workbooks:
Set Wb1 = Workbooks.Open("D:\VBA\file1.xlsx")
Set Wb2 = Workbooks.Open("D:\VBA\file2.xlsx")
Set Wb3 = Workbooks.Open("D:\VBA\file3.xlsx")
Set MainBook = ThisWorkbook

For i = 1 To 3
        For j = A To C
            WB(i).Sheets("sheet1").Range("E4").Copy
            MainBook.Sheets("DATA").Range(j & "1").PasteSpecial
            WB(i).Sheets("sheet1").Range("E5").Copy
            MainBook.Sheets("DATA").Range(j & "2").PasteSpecial
        Next j
Next i

MainBook.Save
MainBook.Close End Sub

Basically the macro should take the data from sheet 1 - cells E4 and E5 of Wb1, Wb2, Wb3... and print the data of Wb1 in the active workbook (cells A1 and A2), the data of Wb2 in cells B1 and B2, the data of Wb3 in cells C1 and C2. Somebody can help?

Sam Sam · Accepted Answer · 2018-01-26T19:09:08

Your problem is that WB(i) does not result in Wb1, Wb2 etc.

Replace the for i with

For Each wb In Array(Wb1, Wb2, Wb3)

and then all wb(i) with wb

VBA loop through workbooks + copy&paste

1 Answers