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 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)
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.
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
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.