Search Contextures Sites

Excel Pivot Table Tutorial -- Pivot Cache
Show the Pivot Table's CacheIndex Show the Pivot Cache Memory Used   Show the Pivot Cache Record Count   Change the Pivot Cache   Pivot Table Tutorial List Download the zipped sample file for this pivot table tutorial	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 '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 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 Download the zipped sample file for this pivot table tutorial

 

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

Pivot Table Books Pivot Tables, Beginning (Excel 2007)  Pivot Tables, Recipe Book (Excel 2003)  Pivot Tables, Recipe Book (Excel 2007)  Pivot Table Add-Ins Pivot Tables - Add-in -- Pivot Power  Pivot Tables - Add-in - Pivot Play PLUS  Pivot Table Videos Pivot Tables - Clear Old Items Pivot Tables - Copy a Custom PivotTable Style Pivot Tables - Create in Excel 2007 Pivot Tables - Create from Multiple Sheets Pivot Tables - Data Field Layout Pivot Tables - Date Filters, Add Pivot Tables - GetPivotData Pivot Tables - Group Data Pivot Tables - Layout, Excel 2007 Pivot Tables - Report Filters, Add Pivot Tables - Running Totals Pivot Tables - Select Sections Pivot Tables - Subtotals, Create Multiple Pivot Tables - Top 10 Filters

 

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2012 All rights reserved.	Debra Dalgleish