PivotCache and Pivot Index

[berlinhammer]

Hello All

I have a workbook that has several Pivots running off of it and potentially more in future.

I am slightly confused about PivotCache and Pivot Index.

Seeing as all of these are running off of the same dataset, I would imagine that it would be more efficient for me to just have one PivotCache? As the code and its output below show I have 6 at the moment for 7 Pivot Tables.

Would anyone be kind enough to help explain what a PivotCache and Index is? I'm not finding much in the way of clear simple definitions googling. Of course I would also like to consolidate my PivotCaches into one with VBA if anyone can help with the code.

I am constructing quite a long and involved macro and I would like to have my Pivots in good order so it will be easy to get them to all refresh when I run the macro. I do currently have a macro that loops through all worksheets and then all pivottables refreshing, but I'd imagine it must be a pretty inefficient way of doing things.

Thanks!

Jon

CODE

Sub PivotCacheAnalysis()

Dim pt As PivotTable
Dim pc As PivotCache
Dim wb As Workbook
Dim ws As Worksheet
Dim pc_cnt As Integer
Dim pt_cnt As Integer
Dim pt_tot As Long

Set wb = ThisWorkbook

    pc_cnt = wb.PivotCaches.Count
    
    Debug.Print vbCrLf & "***PivotCache Count***" & vbCrLf
    Debug.Print pc_cnt

    Debug.Print vbCrLf & "***PivotCache Indices and Source***" & vbCrLf
    For Each pc In wb.PivotCaches
        Debug.Print "*Index*"
        Debug.Print pc.Index
        Debug.Print "*SourceData*"
        Debug.Print pc.SourceData
    Next pc
    
    Debug.Print vbCrLf & "***Worksheets, PivotTable Counts, Names and Caches***" & vbCrLf
    For Each ws In wb.Sheets
        Debug.Print "*Sheet*"
        Debug.Print ws.Name
        pt_cnt = ws.PivotTables.Count
        Debug.Print "*PivotTables*"
        Debug.Print pt_cnt
        
            For Each pt In ws.PivotTables
                Debug.Print "*PivotTable Name*"
                Debug.Print pt.Name
                Debug.Print "*PivotTable Cache*"
                Debug.Print pt.CacheIndex
            Next pt
            
        pt_tot = pt_tot + pt_cnt
    Next ws

    Debug.Print vbCrLf & "***Total Pivot Tables***" & vbCrLf
    Debug.Print pt_tot

End Sub

OUTPUT


***PivotCache Count***

6

***PivotCache Indices and Source***

*Index*
1
*SourceData*
PivotRange
*Index*
2
*SourceData*
PivotRange
*Index*
3
*SourceData*
PivotRange
*Index*
4
*SourceData*
PivotRange
*Index*
5
*SourceData*
PivotRange
*Index*
6
*SourceData*
PivotRange

***Worksheets, PivotTable Counts, Names and Caches***

*Sheet*
Instruction
*PivotTables*
0
*Sheet*
Independent Restrictions
*PivotTables*
0
*Sheet*
Staff Query
*PivotTables*
0
*Sheet*
Committees MoM - Pivot
*PivotTables*
1
*PivotTable Name*
PivotTable1
*PivotTable Cache*
1
*Sheet*
Independents - Pivot
*PivotTables*
3
*PivotTable Name*
PivotTable6
*PivotTable Cache*
3
*PivotTable Name*
PivotTable2
*PivotTable Cache*
2
*PivotTable Name*
PivotTable3
*PivotTable Cache*
1
*Sheet*
ChairVot - Pivot
*PivotTables*
1
*PivotTable Name*
PivotTable1
*PivotTable Cache*
4
*Sheet*
ChairNVot - Pivot
*PivotTables*
1
*PivotTable Name*
PivotTable4
*PivotTable Cache*
5
*Sheet*
ChairInd - Pivot
*PivotTables*
1
*PivotTable Name*
PivotTable5
*PivotTable Cache*
6
*Sheet*
Voter - Count
*PivotTables*
0
*Sheet*
Data
*PivotTables*
0
*Sheet*
SQL
*PivotTables*
0
*Sheet*
Voter2
*PivotTables*
0
*Sheet*
Export
*PivotTables*
0
*Sheet*
Committee Staff
*PivotTables*
0

***Total Pivot Tables***

7

 

[berlinhammer]

Actually just to clarify my post above is a bit unclear.
The code above is just to analyse the PivotCaches and PivotTables in the workbook I'm working on. It has nothing to do with the bigger Macro I'm working on, or the one that loops through the PivotTables refreshing them all that I mentioned.
Sorry
Jon

 

[berlinhammer]

I think I have managed to come up with some code that is doing as I wish

FYI if anyone is interested

Thanks

Jon

Private Sub ConsolidatePivotCache()

Dim wb As Workbook
Dim ws As Worksheet
Dim ptO As PivotTable
Dim pt As PivotTable
Dim pc As PivotCache
Dim pc_cnt As Long

Set wb = ThisWorkbook

'''''''PivotCache Analysis Output'''''''''

'*Sheet*
'Committees MoM - Pivot
'*PivotTables*
'1
'*PivotTable Name*
'PivotTable1
'*PivotTable Cache*
'1

''''''''''''''''''''''''''''''''''''''''''

'Set ptO to be a Pivot table with the Pivot Cache
'you wish to apply to the
'other Pivots in the Workbook

Set ptO = wb.Sheets("Committees MoM - Pivot").PivotTables(1)

''''''''''''

pc_cnt = wb.PivotCaches.Count
Debug.Print pc_cnt
MsgBox pc_cnt & " PivotCaches before Process."

For Each ws In wb.Sheets
    Debug.Print ws.Name
    For Each pt In ws.PivotTables
        Debug.Print pt
        pt.CacheIndex = ptO.CacheIndex
    Next pt
Next ws

pc_cnt = wb.PivotCaches.Count
Debug.Print pc_cnt
MsgBox pc_cnt & " PivotCaches after Process."

Set pc = wb.PivotCaches(1)
pc.Refresh

End Sub

 

[sbparkman]

Many years later, this helped me a bunch! Thank you!