Consolidate function with custom path (reference)

[hordaT]

Hello,

I´m using Consolidate function to sum number in multiple rows and delete duplicates in the same sheet. It works fine, however I need to change source path to make it work when I move workbook to different destination.

   Range("DE1").Select
   Selection.Consolidate Sources:= _
      "'C:\Users\too many folders\[Combine-test.xlsm]Combined'!R1C2:R100000C107" _
        , Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

I´ve tried to change sources path to for example: sheet1.range("A1:DD10000"), however it seems to be not supported here.

Does anybody have any idea how to make it work?

Thank you

 

[hordaT]

hello,

I´ve found quite simple solution...

Dim MyArray As Variant
MyArray = "'" & "sheet_name" & "'!R1C2:R100000C107"

    Range("DE1").Select
    Selection.Consolidate Sources:=MyArray _
        , Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

Now it works no matter if I rename workbook or start if from different location.