I have the following code to refresh a bunch of pivot table caches. For whatever reason, everytime I save and increment the file, the caches keep pointing back to the old file. I can get this to work for one pivot table but not all through the workbook. Any advice?
Code:
Sub AdjustPivotDataRange()
Dim wb As Workbook
Dim ws As Worksheet
Dim pvtCache As PivotCache
Dim SrcRange As String
Dim pt As PivotTable
Set wb = ActiveWorkbook
source = "MasterData" 'name of source data table
Set pvtCache = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=source)
For Each ws In wb.Worksheets
For Each pt In ws.PivotTables
pt.ChangePivotCache pvtCache
Next pt
Next ws
End Sub
