Refresh PT Problem

[mikeymay]

I have a number of pivot tables in different worksheets but all connected to the same data and data range.

All of these sheets are protected and upon a sheet being activates (Worksheet_Activate) the sheet is unprotected and the table in that sheet is refreshed.

This works perfectly well for one sheet when I only unprotect the active sheet but on all the other worksheets, I have to unprotect ALL worksheets that have a pivot table connected to the same data.

I can see that I need to unprotect all worksheets to update a pT but I can't work out why one of the worksheets doen't need all the other worksheets unprotecting.


TIA

 

[RoryA]

It sounds like you actually have two pivot caches.

 

[mikeymay]

The one that doesn't need all unprotecting is based on the same dataset but it was created prior to other columsn being added to the dataset.

The dynamic range used in this pt is the same as all the others though.

 

[RoryA]

That doesn't change what I said. What does Msgbox activeworkbook.pivotcaches.count display if you run it from the immediate window in the VB Editor? 2?

 

[mikeymay]

7

 

[RoryA]

OK, and how many different data sets do you think are in use for pivot tables in the workbook?

 

[mikeymay]

Just 1

 

[RoryA]

Then it looks like either this is an old file (Excel used to default to a separate cache per pivot unless you said otherwise) or something odd happened when the pivot tables were being created as you apparently have 7 different data caches.

 

[mikeymay]

Its a file I created in 2019.

Is it possible to clear all the caches or direct the tables to just one?

 

[RoryA]

You could run something like this - just change the ptMaster reference to any pivot table that you are sure is using the correct range:

Sub resetPivots()
    Dim ptMaster As PivotTable
    Set ptMaster = Sheets("Sheet5").PivotTables(1)
    
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Dim pt As PivotTable
        For Each pt In ws.PivotTables
            If Not pt.Name = ptMaster.Name Then
                pt.CacheIndex = ptMaster.CacheIndex
                pt.RefreshTable
            End If
        Next pt
    Next ws
End Sub