PivotCache and Pivot Index

berlinhammer

Board Regular
Joined
Jan 30, 2009
Messages
187
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! :biggrin:

Jon

CODE

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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

berlinhammer

Board Regular
Joined
Jan 30, 2009
Messages
187
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

Board Regular
Joined
Jan 30, 2009
Messages
187
I think I have managed to come up with some code that is doing as I wish

FYI if anyone is interested

Thanks

Jon

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,823
Messages
5,655,479
Members
418,205
Latest member
jacc99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top