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.
See the structure of the file above. The following is the Summary sheet.
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;
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
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;
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;
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
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;
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
Where Summary, shName and shName2 are the names of the sheets you want to exclude from the consolidation process.