Search Contextures Sites

 

Contextures
Excel news
by email

 

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

 

 

Excel Pivot Table -- 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. Pivot Table Tutorial List

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

Download the Sample File

Download the zipped sample data file for this pivot table tutorial

Learn how to create Excel dashboards.

Pivot Table Tutorials

Pivot Table Introduction 
Clear Old Items in Pivot Table
Create a Pivot Table in Excel 2007 
Custom Calculations 
Data Field Layout
Dynamic Data Source
FAQs - Pivot Tables
Field Settings
Filter Source Data  
Filters, Top 10 
Filters, Report Filters
GetPivotData Function
Grand Totals
Grouping Data
Layout, Excel 2007
Multiple Consolidation Ranges
Pivot Cache   
PivotTable Style
Printing a Pivot Table   
Protection  
Running Totals  
Show and Hide Items 
Sorting
Subtotals 
Summary Functions
Unique Item Count

Pivot Table Books

Beginning Pivot Tables (Excel 2007) 
Pivot Tables Recipe Book (Excel 2003) 
Pivot Tables Recipe Book (Excel 2007) 

Pivot Table Add-Ins

Pivot Power 
Pivot Play PLUS 

Pivot Table Videos

Clear Old Items
Copy a Custom PivotTable Style
Create Pivot Table in Excel 2007
Create Pivot Table from Multiple Sheets
Data Field Layout
Date Filters, Add
GetPivotData
Group Data
Layout, Excel 2007
Report Filters, Add
Running Totals
Select Sections
Subtotals, Create Multiple
Top 10 Filters

 

Learn how to create Excel dashboards.

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright ©2013
All rights reserved.

 

Last updated: January 6, 2013 7:18 PM