How can I create a VBA macro to automatically consolidate data

[SadOu]

hi

I have several Excel workbooks, each containing multiple sheets with similar data structures. I want to develop a VBA macro that consolidates data from specific sheets (e.g., sheets named "Sales" across all workbooks) into a single master sheet. What is the best approach to achieve this, and how can I handle potential issues such as different row counts or missing sheets?

[Aussiebear]

List all workbooks in an Array and use a For each workbook in Array type function

[Aussiebear]

Maybe something like this can give you a hint as to how it might be done. Remember this is fairly generic given that you didn't provide any real details.



Sub UpdateMasterWorkbook()
Dim wbMaster As Workbook
Dim wbSource As Workbook
Dim wsMaster As Worksheet
Dim wsSource As Worksheet
Dim rngMaster As Range
Dim rngSource As Range
Dim LRowMaster As Long
Dim LRowSource As Long
Dim i As Long
' Set the path and name of the master workbook
Set wbMaster = Workbooks.Open("C:\Path\To\Your\Master\Workbook.xlsx") '<---Change to suit the path
' Set the worksheet name in the master workbook
Set wsMaster = wbMaster.Sheets("Sheet1") ' <---Replace "Sheet1" with your actual sheet name
' Set the range in the master workbook where you want to update the data
Set rngMaster = wsMaster.Range("A2") ' Adjust the starting cell as needed
' Loop through the source workbooks
For i = 1 To Workbooks.Count
If Workbooks(i).Name <> wbMaster.Name Then
Set wbSource = Workbooks(i)
Set wsSource = wbSource.Sheets("Sheet1") '<--- Replace "Sheet1" with your source sheet name
' Find the last row in the source workbook
lRowSource = wsSource.Cells(Rows.Count, 1).End(xlUp).Row
' Find the last row in the master workbook
lRowMaster = rngMaster.Offset(Rows.Count - 1, 0).End(xlUp).Row
' Copy data from the source workbook to the master workbook
rngSource = wsSource.Range("A2:D" & lastRowSource) ' <---Adjust the range as needed
rngMaster.Offset(lastRowMaster, 0).Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
End If
Next i
' Save the master workbook
wbMaster.Save wbMaster.Close
End Sub

[Jan Karel Pieterse]

Check out Data, Get Data, From File, From Folder.