If you have an Excel workbook that contains several pivot tables and multiple Excel Tables, you might want to identify which pivot cache each pivot table uses.
To figure out which pivot cache a pivot table uses, you can use a macro to check the pivot table’s CacheIndex property and show the index number in a message box.
Add the Pivot Cache Code
The following Excel macro will show the pivot cache index number for the selected pivot table. Copy the code and paste it into a regular code module in an Excel workbook.
Sub ViewCacheIndex()
On Error GoTo err_Handler
MsgBox "PivotCache: " & _
ActiveCell.PivotTable.CacheIndex
Exit Sub
err_Handler:
MsgBox "Active cell is not in a pivot table"
End Sub
Run the Pivot Cache Code
Select a cell in a pivot table, and then run the macro. A message box displays the CacheIndex property for the active cell’s pivot table.
If the active cell is not in a pivot table, an error message is displayed.
How the Macro Works
When a a pivot cache is created, it is added to the workbook’s PivotCaches collection and given an index number. That index number is displayed in the macro’s message box. If a pivot table is based on the same source data as an existing pivot table, it uses the same pivot cache.
__________
You’re awesome! Thank you so much for providing this code. Helped me out of setting up 10 pivot caches and seeing where the cursor crashed. Appreciate it!
Mike, you’re welcome! Thanks for letting me know that the code helped.
Yes the codes are so helpful! Thank you so much!
Hello Debra,
I have multiple Pivot tables in my excel. Data source is same, but i did not run wizard to create new pivot table from existing pivot table. Instead i just copy/paste the pivot table. Based on what i read about optimizing pivot tables, i’m not using same pivot cache, hence file size is big. However, i used your code to check the pivot cache index number and it seems like pivot tables are using same cache.
Do you know what happens in the background when new pivot is added by copy’paste!
In the end, my req is to have optimized file size.
Thanks