PDA

View Full Version : How to consolidate data from multiple sheets into a master excel



musketeer89
01-18-2018, 02:33 AM
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

mana
01-18-2018, 03:52 AM
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

p45cal
01-18-2018, 04:26 AM
cross posted without links at: https://www.mrexcel.com/forum/excel-questions/1039362-how-consolidate-data-multiple-sheets-into-master-excel.html

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

musketeer89
01-18-2018, 04:45 AM
Hi p45cal

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

p45cal
01-18-2018, 04:55 AM
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.

musketeer89
01-18-2018, 11:52 AM
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