Create Pivot Table from Existing Cache

When you create a pivot table in Excel, a pivot cache is automatically created. The pivot cache is a special memory area where the pivot table records are saved. If there are multiple pivot tables in a workbook, they might use the same pivot cache, or different pivot caches. Use these macros to create a new pivot table from an existing pivot cache, and choose a specific cache – by cache number or pivot table location.

Macros to Create Pivot Table from Existing Cache

There are two macro examples below, to create a pivot table from an existing pivot cache. There are more Pivot Cache macros on my Contextures website.

1. The first pivot cache, based on index number, is used for the new pivot table
2. The pivot cache for the first pivot table on a specific worksheet is used for the new pivot table

Copy these macros to a regular code module in your workbook. There are instructions on my Contextures website.

Macro 1: Use the First Pivot Cache

In this macro, The macro creates a new pivot table on a new worksheet, and it uses the first pivot cache  wb.PivotCaches(1).

The macro doesn’t give the pivot table a name, so Excel automatically creates a numbered name for the pivot table, such as PivotTable2.

Sub CreatePivotFromCacheNumber()
Dim wb As Workbook
Dim wsNew As Worksheet
Dim pc As PivotCache
Dim ptNew As PivotTable

Set wb = ActiveWorkbook
Set pc = wb.PivotCaches(1)

Set wsNew = Sheets.Add
Set ptNew = pc.CreatePivotTable(wsNew.Range("A3"))
End Sub

Macro 2: Use a Pivot Table’s Pivot Cache

In this macro, The macro creates a new pivot table on a new worksheet, and it uses the pivot cache for first pivot table on a specific worksheet  — PivotReg.

The macro doesn’t give the pivot table a name, so Excel automatically creates a numbered name for the pivot table, such as PivotTable3.

Sub CreatePivotFromPTCache()
Dim wb As Workbook
Dim wsNew As Worksheet
Dim pc As PivotCache
Dim ptNew As PivotTable

Set wb = ActiveWorkbook
Set pc = wb.Sheets("PivotReg") _
          .PivotTables(1).PivotCache

Set wsNew = Sheets.Add
Set ptNew = pc.CreatePivotTable _
              (wsNew.Range("A3"))
End Sub

Get the Pivot Cache Workbook

To get the sample Excel file that has the pivot cache macros, go to the Pivot Cache page on my Contextures website.

The zipped file is in xlsm format, and contains macros, so be sure to enable macros when you open the workbook, if you want to test the code.

Save