How to specify used ranges for data consolidation with VBA

[techbutterfly]

This is my first time posting here, though I've gotten plenty of answers from these forums in the past.

I am using the data consolidation feature in Excel 2013. Because the two sheets I'm consolidating might have different sized ranges each time, I want to capture the used range for each sheet and use them in the consolidate command. I'm getting a type mismatch, but I'm not sure how to resolve it. Here is my code.

Sub tec_agr_consolidate()
'
' tec_agr_consolidate Macro
'
Dim TEC As Range
Dim IVUE As Range

Worksheets(1).Activate
Set TEC = Worksheets(1).UsedRange

Worksheets(2).Activate
Set IVUE = Worksheets(2).UsedRange

    Sheets.Add After:=ActiveSheet
    Range("A1").Select
    Selection.Consolidate Sources:=Array( _
        TEC, IVUE), _
        Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
        
End Sub

Any help is appreciated!

 

[pgc01]

Hi
Welcome to the board

When in doubt always check the help:

https://docs.microsoft.com/en-us/office/vba/api/excel.range.consolidate

In this case notice:

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.

Instead of the array of ranges, that you used, use an array with the addressed in in R1C1-style notation including the sheet name, for ex.:

Sub tec_agr_consolidate()
'
' tec_agr_consolidate Macro
'
Dim sTEC As String
Dim sIVUE As String

sTEC = Worksheets(1).UsedRange.Address(ReferenceStyle:=xlR1C1, External:=True)

sIVUE = Worksheets(2).UsedRange.Address(ReferenceStyle:=xlR1C1, External:=True)

    Worksheets.Add After:=Worksheets(2)
    Range("A1").Consolidate Sources:=Array( _
        sTEC, sIVUE), _
        Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
        
End Sub

 

[techbutterfly]

Thanks so much for the help - worked great! I had looked at that help page, but did not read the description for source carefully enough. Next time I'll look closer.

Thanks again!

 

[pgc01]

You're welcome. Thanks for the feedback.