How to consolidate data from multiple sheets into a master excel

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

Code:

---

Option Explicit


Sub test()
    Dim wb As Workbook
    Dim Filepath As String
   
    Filepath = "Filepath on the my computer"
   
    Set wb = Workbooks("zMaster.xlsm")
   
    With Workbooks.Open(Filepath & "A.xlsx")
        .Sheets("A1").Cells(1).CurrentRegion.Offset(1).Copy _
            wb.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Sheets("A2").Cells(1).CurrentRegion.Offset(1).Copy _
            wb.Sheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Close False
    End With
   
    With Workbooks.Open(Filepath & "B.xlsx")
        .Sheets("B1").Cells(1).CurrentRegion.Offset(1).Copy _
            wb.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Sheets("C1").Cells(1).CurrentRegion.Offset(1).Copy _
            wb.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Sheets("B2").Cells(1).CurrentRegion.Offset(1).Copy
            wb.Sheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Offset (1)
        .Sheets("C2").Cells(1).CurrentRegion.Offset(1).Copy
            wb.Sheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Offset (1)
        .Close False
    End With


    With Workbooks.Open(Filepath & "C.xlsx")
        .Sheets("D1").Cells(1).CurrentRegion.Offset(1).Copy _
            wb.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Sheets("D2").Cells(1).CurrentRegion.Offset(1).Copy _
            wb.Sheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Close False
    End With
   
    With Workbooks.Open(Filepath & "D.xlsx")
        .Sheets("E1").Cells(1).CurrentRegion.Offset(1).Copy _
            wb.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Sheets("E2").Cells(1).CurrentRegion.Offset(1).Copy _
            wb.Sheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Close False
    End With
   
End Sub

---

cross posted without links at: https://www.mrexcel.com/forum/excel-...ter-excel.html

read http://www.excelguru.ca/content.php?184

Hi p45cal

I am not able to include the link from MrExcel as I am new to the forum.

just miss off the https part:
://www.mrexcel.com/forum/excel-questions/1039362-how-consolidate-data-multiple-sheets-into-master-excel.html
People will know.

Hi Mana,

Thank you for the code - Can the code you have provided be looped so that it extract data from all files in a particular folder? For example one week there will be Workbooks a,b,c,d next week it there will be Workbooks a,b,c,d,e,f.

Any help will be much appreciated!

Best regards