Consolidate active sheet using macro

Question

I am trying to consolidate some data in the current active sheet using a macro.  However my macro keeps referring back to the original sheet that I used to record it.  How can I change the macro so that it always consolidates the range of cells in the active sheet?

Currently the VBA code reads:

Range("O2").Select   

Selection.Consolidate Sources:= _

        "'C:\Users\Sales1\Desktop[TEST FOR RACK - Copy.XLSX]Sheet'!R2C12:R3000C13", _

        Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

Any help would be greatly appreciated!

Thanks,

Ben

Answer 1

Hi,

Please use following code

[Code]

Range("O2").Select

Selection.Consolidate Sources:= _

"'C:\Users\Sales1\Desktop[TEST FOR RACK - Copy.XLSX]" & ActiveWorkbook.Name & "'!" & ActiveCell.Name, _

Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

GetATPUICultureTag = sCulture

Let me know if that helps.

Kind Regards,

Engr. Jamshed Muhammad

Answer 2

It keeps saying:

Cannot open consolidation source file

Ben

Answer 3

Hi,

Range("O2").Select 
Selection.Consolidate Sources:= _ 
"'C:\Users\Sales1\Desktop\[TEST FOR RACK - Copy.XLSX]" & ActiveWorkbook.Name & "'!'!L2:M3000", _ 
Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False 
GetATPUICultureTag = sCulture 

Use this code.

Let me know if that helps.

Kind Regards,

Engr. Jamshed Muhammad

Answer 4

Jamshed,

Thank you, but pasting that code did not work.  Again, I want to completely ignore the original sheet and just consolidate L2:M3000 of the active worksheet only.

Any thoughts?

Ben

Answer 5

The macro will have to be rewritten. For this, I need a sample workbook.

Request you to please prepare and upload a sample / dummy file sans confidential / sensitive data to Onedrive and share the link? It will help me to give prompt and high quality solution.