Identify an Excel Pivot Table Pivot Cache

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.

pivotcache01

If the active cell is not in a pivot table, an error message is displayed.

pivotcache02

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.

__________

4 thoughts on “Identify an Excel Pivot Table Pivot Cache”

  1. 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!

  2. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.