How to dynamically retrieve the range address of data then change the pivot table data source range address?

Question

I am trying to dynamically retrieve the range address of data then change the pivot table data source range address.

There are three sheets involved.

1) ThisWorkbook.Worksheets("sheet1") - main dashboard, has pivot chart and slicer that were moved from sheet2
2) ThisWorkbook.Worksheets("sheet2") - Pivot Table
3) ThisWorkbook.Worksheets("sheet3") - source data that was copied from different workbook before the below code runs.

My code gives

Run-time error 5: Invalid Procedure call or argument

The error points to Set pt = Pivot_sht.PivotTables(PivotName).ChangePivotCache(pc)

I have two other pivot charts on my main sheet. Using the same code they refresh without any issues.

Full code:

Option Explicit

Sub test()
    Dim daMa As Worksheet
    Dim daPerf As Worksheet
    Dim LastRow As Long
    Dim LastCol As Long
    Dim Data_sht As Worksheet
    Dim Pivot_sht As Worksheet
    Dim DataRange As Range
    Dim PivotName As String
    Dim NewRangePH As String
    Dim pt As PivotTable
    Dim pc As PivotCache

    'REFRESHING PERFORMANCE HISTORY

    'Set Variables Equal to Data Sheet and Pivot Sheet
    Set Data_sht = ThisWorkbook.Worksheets("sheet3")
    Set Pivot_sht = ThisWorkbook.Worksheets("sheet2")

    'Enter in Pivot Table Name
    PivotName = "PHPivot"

    With Data_sht
        .Activate

        'Dynamically Retrieve Range Address of Data
        LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
        LastCol = 12

        Set DataRange = Data_sht.Range(Cells(LastRow, 1).Address, Cells(1, LastCol).Address)

    End With

    NewRangePH = Data_sht.Name & "!" & _
    DataRange.Address(ReferenceStyle:=xlR1C1)

    'Make sure every column in data set has a heading and is not blank (error prevention)
    If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then
        MsgBox "One of your data columns has a blank heading." & vbNewLine _
          & "Please fix and re-run!.", vbCritical, "Column Heading Missing!"
        Exit Sub
    End If

    'Change Pivot Table Data Source Range Address
    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRangePH)
    Set pt = Pivot_sht.PivotTables(PivotName).ChangePivotCache(pc)

End Sub

I searched through many Google result and results from Stack Overflow and tried at least 10 different things before posting this question.

When you set datarange you haven't prefixed cells with a sheet, like Data_sht. is this intentional? I can see the sheet activation though — Nathan_Sav
Yes. It is intentional "With Data_sht and .Activate" should do the work to activate the datasheet. Would you choose a different approach? — Michal
the with should be like so then with datasht and .cells( similar to lastRow — Nathan_Sav
I ahev amenede like suggested (Set DataRange = .Range(.Cells(LastRow, 1).Address, .Cells(1, LastCol).Address)), but same issue in line (Set pt = Pivot_sht.PivotTables(PivotName).ChangePivotCache(pc)) — Michal
The argument required by ChangePivotCache is of String data type. docs.microsoft.com/en-us/office/vba/api/…. It isn't clear from your snippet what data type you are feeding but from your syntax it seems to be an object. — Variatus

Max Max · Accepted Answer · 2020-03-17T12:14:22

"set pt = " is worng, you do not want to set a new pivottable. Instead use

Pivot_sht.PivotTables(PivotName).ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    newRangePH, Version:=xlPivotTableVersion15)

How to dynamically retrieve the range address of data then change the pivot table data source range address?

1 Answers