Range.Consolidate in Excel: "Run-time error '1004': Application-defined or object defined error"

Question

I am having issues getting this to work for some reason, to the extent my hair is turning grey. There is some data on sheet 'Test' that I am trying to consolidate on sheet 'finaldata'. Here's a screenshot of data:

Sub ConsolidateData()
    Dim finalDataSheet As Worksheet
    Dim testSheet As Worksheet
    Dim finalDataRange As Range
    Dim testRange As Range
        
    ' Set references to the worksheets
    Set finalDataSheet = ThisWorkbook.Worksheets("finaldata")
    Set testSheet = ThisWorkbook.Worksheets("Test")
    
    ' Set the range to consolidate in the finaldata sheet
    Set finalDataRange = finalDataSheet.Range("A1")
    
    ' Set the range to consolidate from the Test sheet
    Set testRange = testSheet.Range("A:P")
    
    ' Consolidate the data
    finalDataRange.Consolidate Sources:=Array(testRange.Address), Function:=xlSum, LeftColumn:=True
End Sub

I figured out the problem is in the Sources:=Array(testRange.Address) bit, but I can't really figure out what it is.

Hope anyone can help and has an inkling of what I am doing wrong. (I tried asking the wife, but all I got was an endless stream of non-relevant issues).

Thank you,

Mark

Answer 1

The docs state:

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.

Try this (not tested):

Sub ConsolidateData()
    Dim finalDataSheet As Worksheet
    Dim testSheet As Worksheet
    Dim finalDataRange As Range
    Dim testRange As Range
        
    ' Set references to the worksheets
    Set finalDataSheet = ThisWorkbook.Worksheets("finaldata")
    Set testSheet = ThisWorkbook.Worksheets("Test")
    
    ' Set the range to consolidate in the finaldata sheet
    Set finalDataRange = finalDataSheet.Range("A1")
    
    ' Set the range to consolidate from the Test sheet
    Set testRange = testSheet.Range("A:P")
    
    ' Consolidate the data
    finalDataRange.Consolidate _
            Sources:=Array(testSheet.Name & "!" & testRange.Address(, , xlR1C1), _
            Function:=xlSum, _
            LeftColumn:=True
End Sub