These Excel macros will help you work with a pivot cache - a special memory area where pivot table records are saved
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
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
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
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
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
Use the following code to create a new pivot table from an existing pivot cache. There are two macro examples below.
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.
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
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
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
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
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:
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
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.
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 zipped workbook with pivot cache macros . The file is in xlsm format, and contains the macros shown above, with sample data for testing.
Last updated: April 14, 2021 3:35 PM