Range.Consolidate Method (Excel)
Consolidates data from multiple ranges on multiple worksheets into a single range on a single worksheet. Variant.
Syntax
expression .Consolidate(Sources, Function, TopRow, LeftColumn, CreateLinks)
expression A variable that represents a Range object.
Parameters
Name |
Required/Optional |
Data Type |
Description |
|---|---|---|---|
Sources |
Optional |
Variant |
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 |
Optional |
Variant |
One of the constants of XlConsolidationFunction which specifies the type of consolidation. |
TopRow |
Optional |
Variant |
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 |
Optional |
Variant |
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 |
Optional |
Variant |
True to have the consolidation use worksheet links. False to have the consolidation copy the data. The default value is False. |
Return Value
Variant
Example
This example consolidates data from Sheet2 and Sheet3 onto Sheet1, using the SUM function.
Worksheets("Sheet1").Range("A1").Consolidate _
Sources:=Array("Sheet2!R1C1:R37C6", "Sheet3!R1C1:R37C6"), _
Function:=xlSum