VBA to change Pivot Table Source Data Range

[ExcelNoob222]

Hi all,

I get the following error: "Run-Time error '438': Object doesnt support this property or method" with the below code. The code is setting the pivot table source data range.

'   Changes pivot table ranges
    
    Dim sht As Worksheet
    Dim SrcData As String
    Dim pvtCache As PivotCache

'   Determine the data range you want to pivot
    Set sht = ThisWorkbook.Worksheets("Item List")
    LastRow = Worksheets("Item List").Range("J" & Rows.Count).End(xlUp).Row
    SrcData = sht.Name & "!" & Range("A3:BT" & LastRow).Address(ReferenceStyle:=xlR1C1)
  
'   Create New Pivot Cache from Source Data
    Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=SrcData)

'   Change which Pivot Cache the Pivot Table is referring to
    Worksheets("Hierarchy_Pivot").Select
    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache (pvtCache)

 

[ExcelNoob222]

Bump....still havent figured this out and hoping someone has an idea! Thanks.