Excel -- Pivot Tables -- Pivot Cache

  1. Show the Pivot Table's CacheIndex
  2. Show the Pivot Cache Memory Used  
  3. Show the Pivot Cache Record Count  
  4. Change the Pivot Cache  
Download the zipped sample file

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
  Dim pt As PivotTable
  Set pt = rngPT.PivotTable
  GetMemory = ActiveWorkbook _
    .PivotCaches(pt.CacheIndex).MemoryUsed
End Function

 

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

 

1. Pivot Tables -- Dynamic Data Source
2. Pivot Tables -- Data Field Layout
3. Pivot Tables -- Show and Hide Items
4. Pivot Tables -- Clear Old Items
5. Pivot Tables -- Field Settings
6. Pivot Tables -- GetPivotData
7. Pivot Tables -- Grouping Data
8. Pivot Tables -- Multiple Consolidation Ranges
9. Pivot Tables -- Printing   
10. Pivot Tables -- Custom Calculations 
11. Pivot Tables -- Pivot Cache     
12. Pivot Tables -- Protection       
13. Pivot Tables -- Grand Totals
   
 

       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 18, 2008 11:53 PM