Consolidate Method

Applies To

Range object.

Description

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

Syntax

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

expression Required. An expression that returns a Range object.

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. The consolidation function. Can be one of the following XlConsolidationFunction constants: xlAverage, xlCount, xlCountNums, xlMax, xlMin, xlProduct, xlStDev, xlStDevP, xlSum, xlVar, or xlVarP. The default value is xlAverage.

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.

See Also

ConsolidationFunction property, ConsolidationOptions property, ConsolidationSources property, Style property.

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