Search Contextures Sites

Excel -- Pivot Tables -- Pivot Cache Show the Pivot Table's CacheIndex Show the Pivot Cache Memory Used   Show the Pivot Cache Record Count   Change the Pivot Cache   Download the zipped sample file	Amazon.com

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

Pivot Tables -- Introduction  Pivot Tables -- Create a Pivot Table in Excel 2007  Pivot Tables -- Data Field Layout Pivot Tables -- Show and Hide Items Pivot Tables -- Clear Old Items Pivot Tables -- Field Settings Pivot Tables -- GetPivotData Pivot Tables -- Grouping Data Pivot Tables -- Multiple Consolidation Ranges Pivot Tables -- Printing    Pivot Tables -- Custom Calculations  Pivot Tables -- Pivot Cache      Pivot Tables -- Protection   Pivot Tables -- Grand Totals Pivot Tables -- Running Totals   Pivot Tables -- Filter Source Data

 

Home     Excel Tips     Excel Files      Blog    Contact
Privacy Policy		The Excel Store	Debra Dalgleish