Consolidate to Sheets Using a Variant

Excel can limit the number of  sheets in a consolidation procedure with the help of a simple variant.  This article will show some examples using this method.  If the sheets that are being consolidated are in the minority, perhaps just using the sheets you want to populate is an option.  It's possible to either assign the sheet names directly to a Variant in VBA or assign them to a Range. The following is hard coded into the Array so it will loop through Sheets named England, NorthernIreland, Scotland and Wales.  It will copy all of the Data to Sheet5.

Option Explicit
Option Base 1
Sub Combine3() 'Excel VBA to consolidate sheet names using a variant.
Dim ar As Variant
Dim i As Integer

ar=Array("England", "NorthernIreland", "Scotland", "Wales") 'Array of 4 sheets Range("A11", Range("F" & Rows.Count).End(xlUp)(2)).ClearContents

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


Restrict the Sheets to Consolidate Inside the Loop


Or you can dispense with the Variant and just put the Sheet names directly into the Loop.

Option Explicit
Sub Combine4() 'Excel VBA to consolidate sheet names in an array of sheets
Dim ws As Worksheet

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

For Each ws In Sheets(Array("England", "NorthernIreland", "Scotland", "Wales")) 'Loop from sheet 1 - 4
ws.Range("A2", ws.Range("D" & Rows.Count).End(xlUp)).Copy Sheet5.Range("A65536").End(xlUp)(2)
Next ws
End Sub


As the criteria is inside the loop, the only sheets which will be affected in the workbook are those listed in the array above.

Sheets(Array("England", "NorthernIreland", "Scotland", "Wales")

I have highlighted these in purple to make them stand out. These are the Variables which need to be changed to the sheet names you have in your workbook.