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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,997
Messages
5,508,648
Members
408,689
Latest member
SamSan78

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top