Using the consolidate function in VBA to sum data

Question

I am trying to use the consolidate functionality to sum a number of sheets (with the names apples and oranges), with the same template into one master template.

ConsolidateRange is the tab names and the ranges to use in the consolidation.

Master is the name of the sheet that I am trying to output the sum to.

Here is my code:

ConsolidateRange = "apples!R1C1:R37C6, oranges!R1C1:R37C6"
    
    Worksheets("Master").Range("A1").Consolidate _
    Sources:=Array(ConsolidateRange), _
    Function:=xlSum

The error that I'm getting is 'Cannot open consolidation source file 'apples'.

user3598756 · Accepted Answer · 2017-02-09 14:03:02Z

2

you have to declare a Variant and fill it with Array() function

Dim ConsolidateRangeArray As Variant

ConsolidateRangeArray = Array("apples!R1C1:R37C6", "oranges!R1C1:R37C6")

Worksheets("Master").Range("A1").Consolidate _
Sources:=ConsolidateRangeArray, _
Function:=xlSum

or use Split() to make a String array out of a string with substrings separated by a delimiter

Dim ConsolidateRange As String

ConsolidateRange = "apples!R1C1:R37C6,oranges!R1C1:R37C6"

Worksheets("Master").Range("A1").Consolidate _
Sources:=Split(ConsolidateRange, ","), _
Function:=xlSum

edited Feb 9, 2017 at 14:03

answered Feb 9, 2017 at 13:56

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Stacey Over a year ago

Thanks, but when I try I still getting the Cannot open consolidation source file 'apples' error

user3598756 Over a year ago

are those worksheets both in the same, active, workbook?

Stacey Over a year ago

Hi, apples and oranges are tabs in the active workbook

user3598756 Over a year ago

both my codes (as you see them in their last edit) work with sheets named after "Master", "apples" and "oranges". So double check your code for every single statement remembering that all commas and spaces must be where you see them and must not be where you don't see them

Stacey Over a year ago

Thanks my bad the file name was ' apples' (with a space at the front and not 'apples', works great thanks!

Collectives™ on Stack Overflow

Using the consolidate function in VBA to sum data

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related