Excel Macro: Make All PivotTables Use the Same Pivot Cache

If you work enough with PivotTables, you will no doubt find that you need to analyze the same dataset in multiple ways. In most cases, this process requires you to create separate PivotTables from the same data source.

The problem is that every time you create a PivotTable, you store a snapshot of the data source in the pivot cache. Every time you create a perspective cache, it increases your memory usage and file size. Side effects of this behavior A side effect of this behavior is that your spreadsheet will bloat with too much data. Having your PivotTables share the same cache prevents this.

Starting with Excel 2007, Microsoft has built in an automatic pivot cache sharing algorithm that recognizes that you are creating a pivot table from the same source as an existing PivotTable. This reduces the creation of redundant perspective caches. However, if the number of rows or columns captured from your source range is different for each of your PivotTables, you can still inadvertently create multiple pivot caches.

Benefits of a Shared Pivot Cache

• Reduce file size.
• You can refresh a PivotTable and all other data sharing the Pivot Cache will also be refreshed.
• When you add a calculated field to one PivotTable, your newly created calculated field appears in the fields list of the other PivotTable.
• When you add a calculated item to one PivotTable, it also appears in other PivotTables.
• Any grouping or ungrouping you do affects all PivotTables that share the same cache.

Make All PivotTables Use the Same Pivot Cache

Sub SamePivotCache()
'Step 1: Declare your Variables
    Dim ws As Worksheet
    Dim pt As PivotTable
'Step 2: Loop through each sheet in workbook
    For Each ws In ThisWorkbook.Worksheets
'Step 3: Loop through each PivotTable
        For Each pt In ws.PivotTables
            pt.CacheIndex = Sheets("Sales").PivotTables("PivotTable1").CacheIndex
        Next pt
    Next ws
End Sub

How This Macro Works

1. Step 1 declares an object called ws. This creates a memory container for each worksheet we loop through. We also declare an object called pt, which holds each PivotTable we loop through.

2. Step 2 uses For Each...Next statement to start the looping, telling Excel we want to evaluate all worksheets in this workbook. Notice we are using ThisWorkbook instead of ActiveWorkbook. ThisWorkbook object refers to the workbook that the code is contained in. ActiveWorkbook object refers to the workbook that is currently active. They often return the same object, but if the work book running the code is not the active workbook, they return different objects. In this case, we don’t want to risk affecting PivotTables in other workbooks, so we use ThisWorkbook.

3. Step 3 uses For Each...Next statement to loops through all the PivotTables in each worksheet, and then sets the CacheIndex to the same one used by PivotTable1 on the "Sales" sheet. After all PivotTables have been refreshed, we move to the next sheet. After all sheets have been evaluated, the macro ends.

How to Use This Macro