How to consolidate data from multiple sheets into a master excel

[musketeer89]

Hi Everyone,

I have posted the same question on MrExcel as well: mrexcel.com/forum/excel-questions/1039362-how-consolidate-data-multiple-sheets-into-master-excel.


I was hoping if someone could help me with a VBA code. I already have a VBA code which imports data from 'sheet 1' of multiple workbooks into a Master Workbook (xMaster) file. However, I want to adapt the code so that it can import data from 'sheet 2' of multiple workbooks into sheet 2 of the Master Workbook file. Please note data on Sheet 1 and Sheet 2 are different and need to be consolidated on a separate sheet on the master workbook.


For ease I have tried to explain it below:


Workbook A = Sheet A1 & Sheet A2
Workbook B = Sheet B1 & Sheet B2
Workbook B = Sheet C1 & Sheet C2
Workbook C = Sheet D1 & Sheet D2
Workbook D = Sheet E1 & Sheet E2
xMaster Workbook = Master Sheet 1 + Master sheet 2


Master sheet 1 = Sheet A1 + B1 + C1 + D1 + E1
Master sheet 2 = Sheet A2 + B2 + C2 + D2 + E2


My current code:


Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
Dim Filepath As String
Filepath = "Filepath on the my computer"
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
If MyFile = "zMaster.xlsm" Then
Exit Sub
End If




Workbooks.Open (Filepath & MyFile)
Range("A2:G300").Copy
ActiveWorkbook.Close




erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Data").Range(Cells(erow, 1), Cells(erow, 7))


MyFile = Dir




Loop
End Sub