Consolidate Method

Microsoft Excel Visual Basic

Consolidate Method

       

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

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

expression   Required. An expression that returns one of the objects in the Applies To list.

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

XlConsilidationFunction can be one of these XlConsilidationFunction constants.
xlAverage default.
xlCount
xlCountNums

xlMax

xlMin

xlProduct

xlStDev

xlStDevP

xlSum

xlVar

xlVarP

TopRow  Optional VariantTrue 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 VariantTrue 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 VariantTrue to have the consolidation use worksheet links. False to have the consolidation copy the data. The default value is False.

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