Excel VBA to Consolidate Data which Meets Criteria

This article follows on from the Consolidate sheets article which focuses on combining Excel worksheets with VBA.

Excel VBA can be used to loop through the worksheets in a file, use the autofilter to isolate criteria and copy the information to the last used row in a sheet.  This traps unique information in one movement and consolidate it to a summary sheet.  The unique information is all the data in column A which is called England.

Option Explicit
Sub Combine5() 'Excel VBA to combine data.
Dim ws As Worksheet

For Each ws In Sheets
If ws.Name <> "Summary" Then 'Exclude the Summary sheet from the Loop.
ws.Range("A1", ws.Range("E65536").End(xlUp)).AutoFilter 1, "England"
ws.Range("A2", Range("F65536").End(xlUp)).Copy Sheet1.Range("A65536").End(xlUp)(2)
End If
Next ws

End Sub


The above Excel procedure will bring all of the data in each of the sheets into a summary sheet (Sheet1 Code Name).


Extract Data Out of Summary Sheet


I can update the child sheet from the summary sheet with the same method I used to combine each sheet. Ensure that the sheet names are identical to the criteria, although there are ways to get around the two items being different.

Option Explicit
Option Base 1
Sub ExtracttoSheets() 'Excel VBA to consolidate data.
Dim ar As Variant
Dim i As Integer

ar=Array("England", "Scotland", "Wales", "NorthernIreland")

For i=1 To UBound(ar) 'Loop from 1 to Last item in array (4)
Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, ar(i)
Range("A2", Range("F" & Rows.Count).End(xlUp)).Copy Sheets(ar(i)).Range("A65536").End(xlUp)(2)
Next i
[a1].AutoFilter
End Sub


Once again make sure the sheet names are identical to the names you have in the Array.

ar=Array("England", "Scotland", "Wales", "NorthernIreland")

The following Excel files shows the above VBA examples.

Consolidate Using the Sheet Index Number

The sheet Index number refers to the position of a sheet tab in Excel. So if you have 3 sheets, in VBA the left most sheet will be sheets(1), the middle sheet will be sheets(2) and the right most sheet will be sheets(3) regardless of the sheet name. We can use this to our advantage when writing VBA consolidation code. If you put the consolidation sheet tab at the far right then you can use the following to combine all the sheets in a workbook except the consolidation tab.

Option Explicit
Sub Combine4() 'Excel VBA to consolidate data.
Dim i As Integer

Range("A11", Range("D" & Rows.Count).End(xlUp)(2)).ClearContents

For i=1 To Worksheets.Count - 1 'Loop from from Worksheet Index 1 to the second last tab
Sheets(i).Range("A2", sheets(i).Range("D" & Rows.Count).End(xlUp)).Copy Sheet5.Range("A65536").End(xlUp)(2)
Next i
End Sub


The VBA code will loop from the first sheet from the left to the second last sheet from the right. It is a simple method for consolidating data as it is done just by the intelligent movement of sheets within a workbook.