I wrote the following sub for a workbook containing 12 pivot tables (PT) in 6 sheets, all pointing to the same external source (another workbook). It works fine, except that if I wish to manually Refresh one pivot, Excel tells me I need to open the source file. If I then open the pivot’s source file, the refresh works OK.
I would prefer to have the pivot cache refreshed in the background, like it once was. What am I missing ?
Sub ChangePivotSourceData(src As String) Dim pt As PivotTable, wks As Worksheet, pc As PivotCache 'update #1 pt in PIVOT AF Sheet2.PivotTables(1).ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=src) 'adjust all others For Each wks In ThisWorkbook.Worksheets Debug.Print Now, wks.Name For Each pt In wks.PivotTables Debug.Print Now, pt.Name pt.CacheIndex = Sheet2.PivotTables(1).CacheIndex pt.RefreshTable Next pt Next wks Debug.Print Now, "ChangePivotSourceData complete" End Sub