What can you learn here?
- Space used by pivot cache
- Match cache to pivots
- Clear out cache errors
Using Pivot Caches
Excel Introduced the concept of pivot caches to try to minimize duplicated data when you have multiple pivot tables in your workbook.
Here is how to find out which caches you have, how big they are and which pivot tables are using which caches. Armed with this information you should be able to optimize your pivot usage to decrease the sizeof you workbook.
What are pivot Caches
These are shared cube data that is used by multiple pivot tables in a workbook. They are in a collection called workbook.pivotcaches.
How to get a report of how big each cache is, and which pivot tables are sharing them
This code just outputs the result to a msgBox with a summary of usage for each pivot cache. If you want to make it more elaborate, then format the output to a worksheet.
Sub PivotCacheReport() Dim pc As PivotCache Dim s As String, sn As String Dim ws As Worksheet Dim pt As PivotTable With ActiveWorkbook For Each pc In .PivotCaches s = "Pivotcache " & CStr(pc.Index) & " uses " & CStr(pc.MemoryUsed) & " and has " _ & CStr(pc.RecordCount) & " records" s = s & Chr(10) & "The following pivot tables use it" For Each ws In .Worksheets sn = ws.Name For Each pt In ws.PivotTables If pt.CacheIndex = pc.Index Then If Len(sn) > 0 Then s = s & Chr(10) & sn & Chr(10) sn = "" End If s = s & Replace(pt.Name, "PivotTable", "PT") & "," End If Next pt Next ws MsgBox (s) Next pc sn = Chr(10) & "Couldnt find the pivotcache for these pivot tables" s = "" For Each ws In .Worksheets For Each pt In ws.PivotTables If pt.CacheIndex < 1 Or pt.CacheIndex > .PivotCaches.Count Then s = s & Chr(10) & ws.Name & ":" & Replace(pt.Name, "PivotTable", "PT") End If Next pt Next ws If (Len(s) > 0) Then MsgBox (sn & s) End If End With End Sub
Clearing out failed pivot tables
Sometimes you delete the input to a pivot table. Excel allows these pivot tables dependent on data no longer there to hang around. This will delete any pivot tables in your workbook that are in this invalid state.
Sub PivotCacheClearRubbish() Dim pc As PivotCache Dim ws As Worksheet With ActiveWorkbook For Each pc In .PivotCaches pc.MissingItemsLimit = xlMissingItemsNone Next pc End With End Sub