Macro 65: Make All PivotTables Use the Same Pivot Cache
If you work with PivotTables enough, you will undoubtedly find the need to analyze the same data-
set in multiple ways. In most cases, this process requires you to create separate PivotTables from the
same data source.
The problem is that each time you create a PivotTable, you are storing a snapshot of the data source
in a pivot cache. Every pivot cache that is created increases your memory usage and file size. The side
effect of this behavior is that your spreadsheet bloats with redundant data. Making your PivotTables
share the same cache prevents this.
In addition to the reduction in file size, there are other benefits to sharing a pivot cache:
➤ You can refresh one PivotTable and all others that share the pivot cache are refreshed also.
➤ When you add a Calculated Field to one PivotTable, your newly created calculated field
shows up in the other PivotTables’ field list.
➤ When you add a Calculated Item to one PivotTable, it shows up in the others as well.
➤ Any grouping or ungrouping you perform affects all PivotTables sharing the same cache.
How it works
With the last macro, you are able to take an inventory of all your PivotTables. In that inventory sum-
mary, you can see the pivot cache index of each PivotTable (see Figure 6-1). Using this, you can deter-
mine which PivotTable contains the most appropriate pivot cache, and then force all others to share
the same cache.
In this example, we are forcing all PivotTables to the pivot cache used by PivotTable1 on the Units
Sold sheet.
Sub Macro65()
‘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(“Units Sold”).PivotTables(“PivotTable1”).CacheIndex
Next pt
Next ws
End Sub
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 starts 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 loops through all the PivotTables in each worksheet, and then sets the CachIndex
to the same one used by PivotTable1 on the “Units Sold” 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 it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
