Data Source Change in Macro VB code

[ggeleceng]

Hello Excel Gurus , I have an issue in my macro code, when I run it on a PC other than mine

I digged to find the reason, I noticed the below:



Workbooks("Mastersheet.xlsm").Connections.Add2 _
"WorksheetConnection_RTP Summary!$B:$S", "", _
"WORKSHEET;C:\Users\Ggeleceng\Desktop\Mastersheet.xlsm]RTP Summary" _
, "RTP Summary!$B:$S", 7, True, False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("WorksheetConnection_RTP Summary!$B:$S"), Version _
:=xlPivotTableVersion15).CreatePivotTable TableDestination:= _
"DashBoard!R12C17", TableName:="PivotTable2", DefaultVersion:= _
xlPivotTableVersion15


I need help on how to make the Macro runs on other machines, on other words, how I can modify the pivot table source in the above underlined code to read the data from any machine the macro runs on....

Note: all my data are embedded within the same workbook tabs, no external data

Thanks in advance

 

[RoryA]

Is that code in the Mastersheet.xlsm workbook?

 

[AlphaFrog]

This is a bit of a guess, but try something like this.

[COLOR=#ff0000]ThisWorkbook.[/COLOR]Connections.Add2 _
"WorksheetConnection_RTP Summary!$B:$S", "", _
"WORKSHEET;[COLOR=#ff0000]" & ThisWorkbook.FullName & "][/COLOR]RTP Summary" _
, "RTP Summary!$B:$S", 7, [COLOR=darkblue]True[/COLOR], [COLOR=darkblue]False[/COLOR]
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("WorksheetConnection_RTP Summary!$B:$S"), Version _
:=xlPivotTableVersion15).CreatePivotTable TableDestination:= _
"DashBoard!R12C17", TableName:="PivotTable2", DefaultVersion:= _
xlPivotTableVersion15

 

[ggeleceng]

Is that code in the Mastersheet.xlsm workbook?

Hello RoryA, yes

 

[RoryA]

Then AlphaFrog's amendments should work for you.