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 by using the SUM function.
Worksheets("Sheet1").Range("A1").Consolidate _
Sources:=Array("Sheet2!R1C1:R37C6", "Sheet3!R1C1:R37C6"), _
Function:=xlSum