ExcelNoob222
Board Regular
- Joined
- Jun 17, 2020
- Messages
- 77
- Office Version
- 365
- Platform
- Windows
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.
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.
VBA Code:
' 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)