(Solved) – Refreshing Pivot requires source workbook to be open

  • by

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
        Next pt
    Next wks

    Debug.Print Now, "ChangePivotSourceData complete"
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *