Consolidate Method

Applies To

Range Object.

Description

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

Syntax

object.Consolidate(sources, function, topRow, leftColumn, createLinks)

object

Required. The destination range.

sources

Optional. Specifies 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 consolidate.

function

Optional. The consolidation function (one of xlAverage, xlCount, xlCountNums, xlMax, xlMin, xlProduct, xlStDev, xlStDevP, xlSum, xlVar, or xlVarP).

topRow

Optional. If True, data is consolidated based on column header titles in the top row of the consolidation ranges. If False or omitted, data is consolidated by position.

leftColumn

Optional. If True, data is consolidated based on row titles in the left column of the consolidation ranges. If False or omitted, data is consolidated by position.

createLinks

Optional. If True, the consolidation uses worksheet links. If False, the consolidation copies the data.

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