Search Contextures Sites
Custom Search

 

Contextures
Excel news
by email

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Excel Pivot Table -- Pivot Cache

If you build a pivot table in an Excel file, the source data records are saved in a pivot cache, which is a special memory are. This page has sample code for working with the pivot cache.

  1. Show the Pivot Table's CacheIndex
  2. Show the Pivot Cache Memory Used  
  3. Show the Pivot Cache Count
  4. Show the Pivot Cache Record Count  
  5. Change the Pivot Cache  
  6. Create New Pivot Cache for Selected Pivot Table
  7. Remove Duplicate Pivot Caches
  8. Download the Sample Files
  9. Pivot Table Resources

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(A3)

replacing A3 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(A3)/1000

replacing A3 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.

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(A3)

replacing A3 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 New Pivot 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, you can 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

Download the Sample Files

Download the zipped sample data file for this pivot table tutorial

Download the sample file with the Remove Duplicate Pivot Cache code.

 

More Pivot Table Resources

Tutorials:

__

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.

 

Last updated: June 2, 2016 7:11 PM