Consolidate Workbook with VBA

Consolidate Data to a Summary Sheet

Taking the information from a number of Excel Sheets (Child sheets) and populating a Summary sheet (Parent Sheet) with all of the data in the Child sheets, is a common request in VBA forums.  It is very handy to have the sum of the parts if the parts are growing from the addition of new sheets into a workbook.  An example would be a new month being added to a file in a separate sheet tab. A very quick way to add the new sheet information to a summary tab is to loop through all of the sheets in a workbook and consolidate the data with VBA. Picture the following set up, you have 4 tabs and a summary. In the following example, England, Scotland, Wales and Northern Ireland tabs need to be consolidated into the Summary tab.

Consolidate sheets

See the structure of the file above. The following is the Summary sheet.

Consolidate with VBA

The data has to go into the Green area above from all the child sheets.

The trick is to exclude the Summary sheet from the procedure so only the Child sheets or raw data is copied into the Summary sheet. The following VBA procedure will perform this action;

Option Explicit
Sub Combine1() 'Excel VBA to consolidate all sheets (except summary)
Dim ws As Worksheet Dim sh As Worksheet
Set sh=Sheet5 ' Summary Sheet sh.Range("A2", sh.Range("D65536").End(xlUp)).Clear

For Each ws In Sheets
If ws.Name <> "Summary" Then
ws.Range("A2", ws.Range("D" & Rows.Count).End(xlUp)).Copy sh.Range("A" & Rows.Count).End(xlUp)(2)
End If
Next ws
End Sub


The above is based on the premise that you want to update the Summary tab with all of the data in the current file. It also assumes that there are headers in your Summary sheet. The above clears the Summary sheet but will leave the headers untouched ready for the new data to be pasted into the worksheet. The following is a sample file;

Copy Data to the Bottom of the Range in the Summary Sheet


In addition you can copy data from multiple sheets to the bottom of a range in a Summary sheet. If there is information in the Summary sheets from prior periods or you want to add to the information in the Summary sheet by pushing all of the information to the first blank cell in a range then the following procedure will help with that;

Option Explicit

Sub combine2() 'Excel VBA consolidate but not Summary sheet.
Dim ws As Worksheet
Dim sh As Worksheet
Set sh=Sheet1 ' Summary Sheet

For Each ws In Sheets 'Loop through all sheets Excluding Summary Sheet
If ws.Name <> "Summary" Then
ws.Range("A1", ws.Range("D" & rows.count).End(xlUp)).Copy sh.Range("A65536").End(xlUp)(2)
End If
Next ws
End Sub


Exclude Multiple Sheets from the Loop


If you want to exclude more than one sheet from the loop it might be an option to use an And statement in conjunction with the If statement;

Option Explicit

Sub combine2() 'Excel VBA consolidate but not all sheets.
Dim ws As Worksheet
Dim sh As Worksheet
Set sh=Sheet1 ' SummarySheet

For Each ws In Sheets 'Loop through all sheets Excluding Summary Sheet and 2 others
If ws.Name <> "Summary" and ws.Name <> "shName" and ws.Name <> "shName2" Then
ws.Range("A1", ws.Range("F" & rows.count).End(xlUp)).Copy sh.Range("A65536").End(xlUp)(2)
End If
Next ws
End Sub


Where Summary, shName and shName2 are the names of the sheets you want to exclude from the consolidation process.