Excel Pivot Cache

These Excel macros will help you work with a pivot cache - a special memory area where pivot table records are saved

Show the Pivot Table's CacheIndex

You can display a pivot table's CacheIndex number by using the following User Defined Function. Store the function code in a worksheet module. Then, on the worksheet, enter the formula:

=ShowCacheIndex(B5)

replacing B5 with a cell in your pivot table.

Function ShowCacheIndex(rngPT As Range) As Long
  ShowCacheIndex = rngPT.PivotTable.CacheIndex
End Function      

Show the Pivot Cache Memory Used

You can display the memory used by a pivot cache, by using the following User Defined Function. Store the function code in a worksheet module. Then, on the worksheet, enter the formula:

=GetMemory(B5)/1000

replacing B5 with a cell in your pivot table. The result is displayed in kilobytes.

Function GetMemory(rngPT As Range) As Long
'pivot table tutorial by contextures.com
  Dim pt As PivotTable
  Set pt = rngPT.PivotTable
  GetMemory = ActiveWorkbook _
    .PivotCaches(pt.CacheIndex).MemoryUsed
End Function

Show the Pivot Cache Count

You can display the number of pivot caches in the active workbook, by using the following macro. Store the code in a regular code module.

The macro shows a message box, with the pivot cache count

Sub CountCaches()
  MsgBox "There are " _
      & ActiveWorkbook.PivotCaches.Count _
      & " pivot caches in the active workook."
End Sub   

Show the Pivot Cache Record Count

You can display the number of records in a pivot cache, by using the following User Defined Function. Store the function code in a worksheet module. Then, on the worksheet, enter the formula:

=GetRecords(B5)

replacing B5 with a cell in your pivot table.

Function GetRecords(rngPT As Range) As Long
'pivot table tutorial by contextures.com
  Dim pt As PivotTable
  Set pt = rngPT.PivotTable
  GetRecords = ActiveWorkbook _
    .PivotCaches(pt.CacheIndex).RecordCount
End Function    

Change the Pivot Cache

If you have created several Pivot Tables in a workbook, you may find it more efficient to use the same pivot cache for all the Pivot Tables. The following code will change the pivot cache for each pivot table in the workbook.

Sub ChangePivotCache()
'pivot table tutorial by contextures.com
'change pivot cache for all Pivot Tables in workbook
Dim pt As PivotTable
Dim wks As Worksheet

  For Each wks In ActiveWorkbook.Worksheets
    For Each pt In wks.PivotTables
        pt.CacheIndex = Sheets("Pivot").PivotTables(1).CacheIndex
    Next pt
  Next wks

End Sub

Create Pivot Table From Cache

Use the following code to create a new pivot table from an existing pivot cache. There are two macro examples below.

1. The first pivot cache is used
2. The pivot cache for first pivot table on a specific worksheet is used

NOTE: The macros don't give the pivot tables a name, so Excel automatically creates a numbered name for each pivot table, such as PivotTable1.

Macro 1: The first pivot cache is used -- wb.PivotCaches(1)

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: The pivot cache for first pivot table on a specific worksheet is used

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

Create New Cache for Selected Pivot Table

If two or more pivot tables are based on the same pivot cache, they will share some features, such as calculated items and grouped fields.

To create a separate pivot cache for a pivot table, select a cell in the pivot table, and then run the following code.

The code adds a sheet to the workbook, and creates a new pivot table there, based on the same data source, but in a new pivot cache.

The selected pivot table is set to the same pivot cache as the new table, and then the temporary sheet is deleted.

Sub SelPTNewCache()
    Dim wsTemp As Worksheet
    Dim pt As PivotTable
    
    On Error Resume Next
    Set pt = ActiveCell.PivotTable
    
    If pt Is Nothing Then
        MsgBox "Active cell is not in a pivot table"
    Else
        Set wsTemp = Worksheets.Add
        
        ActiveWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=pt.SourceData).CreatePivotTable _
            TableDestination:=wsTemp.Range("A3"), _
            TableName:="PivotTableTemp"
        
        pt.CacheIndex = wsTemp.PivotTables(1).CacheIndex
        
        Application.DisplayAlerts = False
        wsTemp.Delete
        Application.DisplayAlerts = True
    End If
    
exitHandler:
        Set pt = Nothing

End Sub

Remove Duplicate Pivot Caches

Multiple pivot tables in a workbook may be based on the same data source, but use different pivot caches. This macro creates a list of pivot caches, checks for duplicate data sources, and eliminates duplicate caches.

WARNING: Test this on a copy of your workbook, to ensure that it will work as expected with your data.

Sub CheckCaches()
' Developed by Contextures Inc.
' www.contextures.com
Dim pc As PivotCache
Dim wsList As Worksheet
Dim lRow As Long
Dim lRowPC As Long
Dim pt As PivotTable
Dim ws As Worksheet
Dim lStart As Long
lStart = 2
lRow = lStart

Set wsList = Worksheets.Add
For Each pc In ActiveWorkbook.PivotCaches
  wsList.Cells(lRow, 1).Value = pc.Index
  wsList.Cells(lRow, 2).Value = pc.SourceData
  wsList.Cells(lRow, 3).FormulaR1C1 = _
    "=INDEX(R1C[-2]:R[-1]C[-2],MATCH(RC[-1],R1C[-1]:R[-1]C[-1],0))"
  lRow = lRow + 1
Next pc

For lRowPC = lRow - 1 To lStart Step -1
  With wsList.Cells(lRowPC, 3)
    If IsNumeric(.Value) Then
      For Each ws In ActiveWorkbook.Worksheets
      Debug.Print ws.Name
        For Each pt In ws.PivotTables
        Debug.Print .Offset(0, -2).Value
          If pt.CacheIndex = .Offset(0, -2).Value Then
            pt.CacheIndex = .Value
          End If
        Next pt
      Next ws
    End If
  End With
Next lRowPC

'uncomment lines below to delete the temp worksheet
'Application.DisplayAlerts = False
'wsList.Delete

exitHandler:
Application.DisplayAlerts = True
Exit Sub

errHandler:
MsgBox "Could not change all pivot caches"
Resume exitHandler

End Sub

List All Pivot Caches

Run this macro to list all the pivot caches in the active workbook. It adds a new sheet to the workbook, with a list of pivot caches, with the following details:

1. Cache Index Number
2. Number of Pivot Tables using the cache
3. Number of records in the cache
4. Source Type (xldatabase or Other Source)
5. Data Source (table name or sheet name and address
6. Latest Refresh date and time
7. Is the pivot cache set to refresh on open?

Add the following code to a regular worksheet module, then run the macro to create the list.

Sub ListAllPivotCaches()
' Developed by www.contextures.com
' list all pivot caches in active workbook
Dim pc As PivotCache
Dim wb As Workbook
Dim ws As Worksheet
Dim lRow As Long
Dim wsAll As Worksheet
Dim lPC As Long
Dim lPCs As Long
Dim lFields As Long
Dim lColDate As Long
Dim ptAll As PivotTable
Dim strSource As String
Dim strST As String
Dim rngS As Range
Dim strSourceR1C1 As String
On Error Resume Next
Application.EnableEvents = False

lRow = 1
lFields = 7
lColDate = 6
Set wb = ActiveWorkbook
lPCs = wb.PivotCaches.Count

If lPCs = 0 Then
  MsgBox "No pivot caches in the workbook"
  Exit Sub
End If
  
Set ws = Worksheets.Add
With ws
  .Range(.Cells(1, 1), .Cells(1, lFields)) _
      .Value = Array("Cache Index", _
        "PTs", _
        "Records", _
        "Source Type", _
        "Data Source", _
        "Refresh DateTime", _
        "Refresh Open")
End With

lRow = lRow + 1

For Each pc In wb.PivotCaches
  'count the pivot tables
  lPC = 0
  Select Case pc.SourceType
    Case 1
      strSourceR1C1 = pc.SourceData
      strSource = Application.ConvertFormula("=" & _
          strSourceR1C1, xlR1C1, xlA1)
      strSource = Replace(strSource, "[" & wb.Name & "]", "")
      strSource = Right(strSource, Len(strSource) - 1)
      strST = "xlDatabase"
    Case Else
      strSource = "N/A"
      strST = "Other Source"
  End Select
  
  For Each wsAll In wb.Worksheets
     For Each ptAll In wsAll.PivotTables
        If ptAll.CacheIndex = pc.Index Then
           lPC = lPC + 1
        End If
     Next ptAll
  Next wsAll

  With ws
    On Error Resume Next
     ws.Range(ws.Cells(lRow, 1), _
      ws.Cells(lRow, lFields)).Value = _
       Array(pc.Index, _
        lPC, _
        pc.RecordCount, _
        strST, _
        strSource, _
        pc.RefreshDate, _
        pc.RefreshOnFileOpen)
  End With
  lRow = lRow + 1
Next pc

With ws
  With .Range(.Cells(1, 1), .Cells(1, lFields))
    .EntireRow.Font.Bold = True
    .EntireColumn.AutoFit
  End With
  .Columns(lColDate).NumberFormat _
      = "[$-409]dd-mmm-yyyy h:mm AM/PM;@"
End With
Application.EnableEvents = True

End Sub

Refresh All Pivot Caches

This macro tries to refresh all the pivot caches in the active workbook. If unable to refresh a pivot cache, a message shows the cache's index number, and the error description.

At the end of the macro, a message shows the count of pivot caches, and the count of failed refreshes.

NOTE: This macro temporarily turns off calculation for the 2 worksheets that have formulas with User Defined Functions, such as the GetMemory function in the screen shot below.

If calculation is not turned off, those functions will run automatically, and cause errors.

RefreshAllPivotCaches Macro Code

Copy this code to a regular code module.

Sub RefreshAllPivotCaches()
' Developed by Contextures Inc.
' www.contextures.com
Dim wb As Workbook
Dim lPCs As Long
Dim lPC As Long
Dim lProb As Long
Application.EnableEvents = False
On Error Resume Next

'turn off calc on sheets with UDF formulas
Sheets("PivotRep").EnableCalculation = False
Sheets("PivotProd").EnableCalculation = False

Set wb = ActiveWorkbook
lPCs = wb.PivotCaches.Count

For lPC = 1 To lPCs
  wb.PivotCaches(lPC).Refresh
  If Err.Number <> 0 Then
    MsgBox "Could not refresh pivot cache " & lPC _
      & vbCrLf _
      & "Error: " _
      & vbCrLf _
      & Err.Description
    Err.Clear
    lProb = lProb + 1
  End If
Next lPC

MsgBox "Refresh is complete. " _
  & vbCrLf _
  & "Pivot Cache Count: " & lPCs _
  & vbCrLf _
  & "Failed refreshes: " & lProb

'turn on calc on sheets with UDF formulas
Sheets("PivotRep").EnableCalculation = True
Sheets("PivotProd").EnableCalculation = True

Application.EnableEvents = True

End Sub

Download the Workbook

Download the zipped workbook with pivot cache macros . The file is in xlsm format, and contains the macros shown above, with sample data for testing.

More Pivot Table Tutorials

FAQs - Pivot Tables

Pivot Table Refresh

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table