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 by using the SUM function.
Worksheets("Sheet1").Range("A1").Consolidate _
Sources:=Array("Sheet2!R1C1:R37C6", "Sheet3!R1C1:R37C6"), _
Function:=xlSum
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.