Range.Consolidate (Excel)

Consolidates data from multiple ranges on multiple worksheets into a single range on a single worksheet.

Consolidate (Sources, Function, TopRow, LeftColumn, CreateLinks)

Worksheets("Sheet1").Range("A1").Consolidate _ 
 Sources:=Array("Sheet2!R1C1:R37C6", "Sheet3!R1C1:R37C6"), _ 
 Function:=xlSum

Arguments

The following arguments are optional

Sources (String) - The sources of the consolidation as an array of text reference strings in R1C1-style notation. The references must include the full path of sheets to be consolidated.

Function (XlConsolidationFunction) - One of the constants of XlConsolidationFunction, which specifies the type of consolidation.

Here you can find possible values for XlConsolidationFunction.

TopRow (Boolean) - True to consolidate data based on column titles in the top row of the consolidation ranges. False to consolidate data by position. The default value is False.

LeftColumn (Boolean) - True to consolidate data based on row titles in the left column of the consolidation ranges. False to consolidate data by position. The default value is False.

CreateLinks (Boolean) - True to have the consolidation use worksheet links. False to have the consolidation copy the data. The default value is False.