Dynamically Change Pivot Table Source Data Using VBA

Question

I am trying to write a macro that will dynamically adjust the source data on a pivot table. I have read other solutions on this site and other forums but can't seem to get their "fixes" to work. I am currently using a slightly modified version of some code I found online that seemed to have less issues than others.

My pivot is located on a sheet called "Pivot" and the source data is on a sheet called "Source".

Currently my code is:

Sub AdjustPivotSource()

Dim Data_sht As Worksheet
Dim Pivot_sht As Worksheet
Dim PivotName As String
Dim NewRange As String

'Set Variables Equal to Data Sheet and Pivot Sheet
  Set Data_sht = Worksheets("Source")
  Set Pivot_sht = Worksheets("Pivot")

'Enter in Pivot Table Name
  PivotName = "My_Pivot"

Dim LastRow As Long
LastRow = Worksheets("Source").Range("A" & Rows.count).End(xlUp).Row

 NewRange = ActiveSheet.Name & "!" & Range("$A$1:$DU$" & LastRow).Address(ReferenceStyle:=xlR1C1)


'Change Pivot Table Data Source
  Pivot_sht.PivotTables(PivotName).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
      SourceType:=xlDatabase, _
      SourceData:=NewRange, Version:=xlPivotVersion15)

 'Refresh Pivot
   Pivot_sht.PivotTables(PivotName).RefreshTable
  End Sub

I am getting a "Run-time error '5': Invalid procedure call or argument"error with this section of code highlighted:

 Pivot_sht.PivotTables(PivotName).ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
  SourceType:=xlDatabase, _
  SourceData:=NewRange, Version:=xlPivotVersion15)

My NewRange seems to be accurately grabbing the range I desire. I have verified that the source data has no blank headers. I have double checked that the pivot table name and sheet names are correct.

I'm wondering if the "ThisWorkbook" in the erroring section of code could be an issue? I originally was using:

 Set pivotDataSheet = ThisWorkbook.Worksheets("Source")

instead of:

 Set pivotDataSheet = Worksheets("Source")

This was resulting in the following error:

Run-time error '9': Subscript out of range"

I removed the "ThisWorkbook" and that resolved that error, but I have not had any luck removing "ThisWorkbook" from the part of the code that is giving me an error currently.

"ThisWorkbook" was giving the error because I am running the macro from PERSONAL rather than within the file itself. Still having the main issue.

RugsKid RugsKid · Accepted Answer · 2019-07-25T20:32:28

While not a true fix on the code I had an issue with, the below code works around my issue. I make the source data a Named Table and then set the source of the pivot to the table. This allows me to not reset the pivot cache / data source each time.

Worksheets("Source").ListObjects.Add(xlSrcRange, Range(Cells(1, "A").End(xlDown), Cells(1, "A").End(xlToRight)), , xlYes).Name = "Source_Table"

Dynamically Change Pivot Table Source Data Using VBA

1 Answers