Hi All,
I'm trying to create a macro to update multiple pivot tables in one worksheet with an expanded source data location. For example, I have added more data for a new quarter to the range that the pivot tables are reading from and I wanted to automate the process of updated the data source for all the pivot tables.
Any suggestions on how to do this?
I tried doing this (see below) but I was getting an error that "ChangePivotCache" is a "Sub or Function not defined":
Dim Sheet As Worksheet, Pivot As PivotTable
For Each Sheet In ThisWorkbook.Worksheets
For Each Pivot In Sheet.PivotTables
ChangePivotCache _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"....file path...." R1C1:R5164C42" _
, Version:=xlPivotTableVersion15)
Next
Next
I'm trying to create a macro to update multiple pivot tables in one worksheet with an expanded source data location. For example, I have added more data for a new quarter to the range that the pivot tables are reading from and I wanted to automate the process of updated the data source for all the pivot tables.
Any suggestions on how to do this?
I tried doing this (see below) but I was getting an error that "ChangePivotCache" is a "Sub or Function not defined":
Dim Sheet As Worksheet, Pivot As PivotTable
For Each Sheet In ThisWorkbook.Worksheets
For Each Pivot In Sheet.PivotTables
ChangePivotCache _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"....file path...." R1C1:R5164C42" _
, Version:=xlPivotTableVersion15)
Next
Next