Changing source of Pivot Table

Question

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

Shai Rado · Accepted Answer · 2018-03-14 13:54:37Z

1

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

edited Mar 14, 2018 at 13:54

answered Mar 14, 2018 at 12:46

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shai Rado Over a year ago

@BatyrNazarov try edited code, see what you get in the Debug.Print line

Batyr Nazarov Over a year ago

Thanks Shai. I will do it

SJR · Accepted Answer · 2018-03-14 11:08:50Z

0

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

answered Mar 14, 2018 at 11:08

SJR

23.1k6 gold badges21 silver badges26 bronze badges

2 Comments

Batyr Nazarov Over a year ago

It gives an error message saying Reference is invalid when I run it

Batyr Nazarov Over a year ago

Thanks SJR I will try to do it

Collectives™ on Stack Overflow

Changing source of Pivot Table

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related