Search Contextures Sites

Contextures Excel news by email Email: Name (optional): We respect your email privacy	Excel Pivot Table -- Pivot Cache Show the Pivot Table's CacheIndex Show the Pivot Cache Memory Used   Show the Pivot Cache Count Show the Pivot Cache Record Count   Change the Pivot Cache   Create New Pivot Cache for Selected Pivot Table 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

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

 

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