Changing source of Pivot Table

1
votes

I have the following code which worked at some point but now throws me an error at ".SourceData = rng.Address(True, True, xlR1C1, True)" 

Dim rng As Range
Set rng = ActiveSheet.Range("A1:F" & LastRow)
Set shTotalsPivot = ActiveWorkbook.Sheets("Totals Pivot")


With shTotalsPivot.PivotTables(1).PivotCache
   .SourceData = rng.Address(True, True, xlR1C1, True)
   .Refresh
End With

Could you please advise what I am doing incorrectly. I simply want to change a source in the existing pivot table to the new sheet which will an active sheet in this case. Thanks

2
vbaexcel

2 Answers

1
votes

In order to include the sheet's name of the Range.Address, the 4th parameter needs to be xlExternal.

See modified code below:

Dim shTotalsPivot As Worksheet
Dim Rng As Range
Dim RngString As String
Dim PvtTbl As PivotTable

Set Rng = ActiveSheet.Range("A1:F" & LastRow)

' put the full range address (including sheet name) in a String variable
RngString = Rng.Address(False, False, xlA1, xlExternal)

Set shTotalsPivot = ActiveWorkbook.Sheets("Totals Pivot")

'set the Pivot-Table object
Set PvtTbl = shTotalsPivot.PivotTables(1)

' === for DEBUG ONLY ===
Debug.Pring RngString

' update the Pivot-Cache
With PvtTbl.PivotCache
   .SourceData = RngString
   .Refresh
End With
0
votes

See if this works. The source must be a string and include the sheet name, and you are referring to a pre-existing PT, hence the use of ChangePivotCache.

shTotalsPivot.PivotTables(1).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=ActiveSheet.Name & "!" & rng.Address(True, True, xlR1C1, True))
shTotalsPivot.PivotTables(1).refresh