Returning pivot table source data as string

[ToniGo]

I am having a bizarre issue. What I am doing is copying a worksheet containing pivot tables from one workbook to another. Then I am changing the source data to link to the new workbook, to the same range as the original source but in the new book. My issue is that the original source data is returning a different source to what I see when I go "change data source" manually.

Sub testpivot()
Dim x
Dim OldSource, newSource As String


Dim PT As PivotTable

For Each PT In ThisWorkbook.Sheets("Test Sheet").PivotTables

OldSource = PT.SourceData 

OldSource = VBA.Mid(OldSource, InStr(1, OldSource, "["), 100) 
newSource = Replace(OldSource, "Original Book.xlsm", "")
newSource = Replace(newSource, "[]", "")
 
PT.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                        SourceData:=newSource)

Next PT

End Sub

This almost works but the "oldsource" returned is not my actual data source - its the right worksheet but the range "c5:c10" instead of "$E:$J" which is what the pivot source is when I look at it.

Any suggestions would be much appreciated!!

 

[RoryA]

C5:c10 is the R1C1 reference style equivalent of $E:$J - it means Column 5 to Column 10.

 

[ToniGo]

Thanks Rory, I JUST worked that out (I did post this but it doesn't seem to have come up). Now I am just trying to work out how to convert that reference to a "normal" range reference - this is still very weird because when I take that source data as supplied and apply it as the new source data, it then converts itself back to the other A1 style, and uses that range as the new source c5:c10 so it appears to rely on a different format to input compared to what it outputs..

 

[RoryA]

It's better not to use entire columns as the source data as it's more efficient to use a specific range. If you specify the rows as well - e.g. R1C5:R500C10 - there is no room for ambiguity.

 

[ToniGo]

Thanks for the tip I am actually just trying to cover all possibilities as users may create their own worksheets and pivot tables...then I am trying to move their own workings into an updated template without losing anything that they have created in the meantime. Very close now!