Excel Pivot Table Properties & Settings, using VBA
Excel Pivot Table: Get data from a PivotTable, DrillDown of PivotTable Fields, Display & Sort the PivotTable FieldList, Return the Parent object, Manual Update, Name of a PivotTable object, Print Settings, Save the PivotCache data, create a new PivotTable for each Page field item, return the Data Source, include hidden page field items in totals, Pivot Table versions.
Related Links:
1. Create an Excel Pivot Table report using VBA
2. Referencing an Excel Pivot Table Range using VBA
3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA
4. Excel Pivot Table Address, Location, Move & Copy using VBA
5. Excel Pivot Table Layout and Design, using VBA
6. Excel Pivot Table Properties & Settings, using VBA
7. Refresh Excel Pivot Table and Cache of PivotTable, using VBA
8. Excel Pivot Tables Grouping: Group Items, Group Data and Group Date Values, using VBA
9. Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA
10. Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA
11. Excel Pivot Tables: Summary Functions, Custom Calculations & Value Field Settings, using VBA
12. Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas using VBA
13. Create & Customize Excel Pivot Table Charts, using VBA
Refer complete Tutorial on using Pivot Tables in Excel Window (user interface):
Create and Customize a Pivot Table report
------------------------------------------------------------------------------------------------------------
Contents:
Get data from a PivotTable using VBA code
Disable or Enable DrillDown of PivotTable Fields
Display the PivotTable FieldList
Sort Fields in Ascending order in the PivotTable FieldList
Return the Parent object of a PivotTable object
Allow PivotTable Update only Manually
PivotTable.Name Property returns the name of a PivotTable object
To save the PivotCache data while saving the workbook
Create a new PivotTable, each on a new worksheet, for each item of a Page field
Return the data source of a PivotTable report
Include hidden page field items in subtotals and totals
Pivot Table versions & Excel version
------------------------------------------------------------------------------------------------------------
Get data from a PivotTable using VBA code
PivotTable.GetPivotData Method: gets data from a PivotTable using VBA code - it returns a Range which gives information on a data item. Syntax: PivotTableObjectVariable.GetPivotData(DataField, Field1, Item1, Field2, Item2, ... Field14, Item14). All arguments are optional to specify. Field1 (Field2, ...) is a column or row field name and Item1 (Item2, ...) is the item name in that particular field.
Get total sales figure from the PivotTable - refer Image 1a, after running below code:
Sub getPivotTableData1a()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
MsgBox PvtTbl.GetPivotData("Sales")
End Sub
Get sales figure for Compact Car Model, from the PivotTable - refer Image 1b, after running below code:
Sub getPivotTableData1b()
Dim PvtTbl As PivotTable
Dim rng As Range
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
Set rng = PvtTbl.GetPivotData("Sales", "Car Models", "Compact")
MsgBox rng.value
End Sub
Get sales figure for the Country of Canada, for Compact Car Model, from the PivotTable - refer Image 1c, after running below code:
Sub getPivotTableData1c()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
MsgBox PvtTbl.GetPivotData("Sales", "Country", "Canada", "Car Models", "Compact")
End Sub
Get Budgeted sales figure for MidSize Car Model, for the Country of France, from the PivotTable - refer Image 1d, after running below code:
Sub getPivotTableData1d()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
MsgBox PvtTbl.GetPivotData("Budgeted Sales", "Car Models", "MidSize", "Country", "France")
End Sub
Disable or Enable DrillDown of PivotTable Fields
To disable or enable drilldown for all fields in a PivotTable, use the PivotTable.EnableDrilldown Property. Set True to enable drilldown and False to disable drilldown. Default value is True. This equates to selecting or clearing the check box of 'Enable show details' in the 'PivotTable Options' dialog box.
By enabling Show Details or Drill Down, you can view details of data for a value field, which shows the basis on which the value is arrived at (ie. drills down to detailed data from the data source), select a cell in the values area and right click and then select 'Show Details'. A new worksheet gets created which shows the detailed data. Alternate method to show details is to double-click a cell in the values area.
Code to enable DrillDown:
Worksheets("Sheet1").PivotTables("PivotTable1").
EnableDrilldown = True
Display the PivotTable FieldList
To determine the ability to display the PivotTable FieldList, use the PivotTable.EnableFieldList Property. If set to False, the FieldList will not be able to be displayed and in case it is already being displayed, it will get
hidden. Default value is True.
Code to Hide PivotTable Field List pane:
Worksheets("Sheet1").PivotTables("PivotTable1").
EnableFieldList = False
Sort Fields in Ascending order in the PivotTable FieldList:
PivotTable.FieldListSortAscending Property - the fields are sorted in ascending order in the PivotTable FieldList if set to True, whereas the fields are sorted in the order as they appear in data source if set to False.
Code to Sort Fields in Ascending order in the PivotTable Field List pane:
Worksheets("Sheet1").PivotTables("PivotTable1").
FieldListSortAscending = True
Return the Parent object of a PivotTable object:
To return the Parent object of a PivotTable object, use the PivotTable.Parent Property.
Following code returns the PivotTable (ie. "PivotTable1") which is the Parent of the PivotField (ie. "Country"):
MsgBox Worksheets("Sheet1").PivotTables("PivotTable1").
PivotFields("Country").Parent
Following code returns the PivotField (ie. "Country") which is the Parent of the PivotItem (ie. "France"):
MsgBox Worksheets("Sheet1").PivotTables("PivotTable1").
PivotFields("Country").PivotItems("France").Parent
Allow PivotTable Update only Manually
PivotTable.ManualUpdate Property: Default value of ManualUpdate property is False wherein a PivotTable is recalculated automatically on each change. Set value to True to disallow automatic update in which case the PivotTable can be updated only manually by the user.
Codes to set manual update of PivotTable:
1. Refer Image 2a for PivotTable display before running code.
2. Refer Image 2b for PivotTable display after running below code - manual update is set to True here so PivotTable changes are not updated:
Sub PivotTableManUpdt1()
'turn off automatic updation of Pivot Table:
Worksheets("Sheet1").PivotTables("PivotTable1").ManualUpdate = True
'make changes in PivotTable:
Worksheets("Sheet1").PivotTables("PivotTable1").
PivotFields("City").PivotItems("London").Visible = False
End Sub
3. Refer Image 2c for PivotTable display after running below code - manual update is finally set to False to update changes made in PivotTable:
Sub PivotTableManUpdt2()
'turn off automatic updation of Pivot Table:
Worksheets("Sheet1").PivotTables("PivotTable1").
ManualUpdate = True
'make changes in PivotTable:
Worksheets("Sheet1").PivotTables("PivotTable1").
PivotFields("City").PivotItems("London").Visible = False
'turn on automatic update / calculation in the Pivot Table:
Worksheets("Sheet1").PivotTables("PivotTable1").
ManualUpdate = False
End Sub
PivotTable.Name Property returns the name of a PivotTable object
Code to return the PivotTable name:
MsgBox Worksheets("Sheet1").PivotTables("PivotTable1").Name
Code to return the name of all items in the PivotTable Field of "Country":
Sub PivotTableObjName()
Dim pvtItm As PivotItem
For Each pvtItm In Worksheets("Sheet1").
PivotTables("PivotTable1").PivotFields("Country").
PivotItems
MsgBox pvtItm.Name
Next
End Sub
1. PivotTable.PrintTitles Property: Set to True to use print titles from the PivotTable. The False setting uses print titles from the worksheet. Default value is False.
Code to print titles from the PivotTable:
Sub PivotTablePrint1()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")
PvtTbl.PrintTitles = True
PvtTbl.PivotCache.Refresh
'check print preview:
PvtTbl.Parent.PrintOut , , , True
End Sub
2. PivotTable.RepeatItemsOnEachPrintedPage Property. Set to True to repeat row, column, and item labels on each printed page of the PivotTable. False setting will not repeat the labels while printing. Default value is True.
Code to repeat items on each printed page:
Worksheets("Sheet1").PivotTables("PivotTable1").
RepeatItemsOnEachPrintedPage = True
3. Repeat specific rows at the top or columns on the left while printing a PivotTable report:
Code to print titles - rows to repeat at top:
Worksheets("Sheet1").PageSetup.PrintTitleRows = "$6:$8"
Code to print titles - columns to repeat at left:
Worksheets("Sheet1").PageSetup.PrintTitleColumns = "$C:$D"
4. PivotTable.PrintDrillIndicators Property: If you have chosen to show expand/collapse buttons in your PivotTable, then you can also print these buttons by setting the property to True.
Code to print drill indicators:
Worksheets("Sheet1").PivotTables("PivotTable1").
PrintDrillIndicators = True
To save the PivotCache data while saving the workbook
PivotTable.SaveData Property: Set to True to save the PivotCache data while saving the workbook. In the False setting the cached data does not get saved while saving the workbook.
Code to save the PivotCache data while saving the workbook:
Worksheets("Sheet1").PivotTables("PivotTable1").SaveData = True
Create a new PivotTable, each on a new worksheet, for each item of a Page field
PivotTable.ShowPages Method creates a new PivotTable, each on a new worksheet, for each item of a Page field.
Code to create a new PivotTable report for each item in the "Year" Page Field:
Worksheets("Sheet1").PivotTables("PivotTable1").ShowPages "Year"
Return the data source of a PivotTable report
PivotTable.SourceData Property returns the data source of a PivotTable report
Code:
MsgBox Worksheets("Sheet1").PivotTables("PivotTable1").SourceData
Include hidden page field items in subtotals and totals
PivotTable.SubtotalHiddenPageItems Property: Set to True to include hidden page field items in subtotals and totals. Default value is False. Note: If "Subtotal filtered page items" is greyed out in the PivotTable Options dialog box, then this will give an "invalid procedure call or argument" error.
Code to include hidden page field items in subtotals and totals:
Worksheets("Sheet1").PivotTables("PivotTable1").
SubtotalHiddenPageItems = True
Pivot Table versions & Excel version
It may be noted that there are differences between Pivot Tables created in Excel 2000, Excel 2002-03, Excel 2007 and Excel 2010. Hence it is important to determine in which version of Excel is vba being executed in, to ensure compatibility and similar default settings between the PivotTable with the corresponding Excel version.
You can create a PivotTable with a specific version in Excel 2007 by using the PivotCaches.Create Method.
In Excel 2007, you can have fully interactive PivotTables versions 0 to 3. In Excel versions prior to Excel 2007, you can have fully interactive PivotTable version numbers 0 to 2 (ie. Excel 2000 & Excel 2002-03 formats), but only a read-only PivotTable version number 3 (ie. Excel 2007 format) and you cannot create a PivotChart from this read-only PivotTable.
Working in Excel 2007 in compatibility mode (ie. when xls file is opened) will save an Excel 2000 or Excel 2002-03 format PivotTable (ie. PivotTable versions 0 to 2). When you save an Excel 2007 file in compatibility mode, all PivotTables of previous versions (0 to 2) will be marked for upgrade to version 3 (xlPivotTableVersion12) and the respective PivotTable will be upgraded when refreshed next. Once the PivotTable is upgraded and converted to version 3, it cannot be converted back to an earlier version.
All features of Excel 2007 can be used when you use a PivotTable version number 3 (ie. Excel 2007 format) in Excel 2007. Using PivotTable version numbers 0 to 2 (ie. Excel 2000 & Excel 2002-03 formats) in Excel 2007 will enable use of most but not all features (esp. some filtering options) of Excel 2007.
See below in the order of: PivotTable Version, PivotTable Version Number, Excel Version, Excel Version Number
xlPivotTableVersion2000, 0, Excel 2000, 9.0
xlPivotTableVersion10, 1, Excel 2002, 10.0
xlPivotTableVersion11, 2, Excel 2003, 11.0
xlPivotTableVersion12, 3, Excel 2007, 12.0
xlPivotTableVersion14, 4, Excel 2010, 14.0
Note: There is no difference in behavior between xlPivotTableVersion10 and xlPivotTableVersion11.
Application.Version Property - returns the Excel version number in which VBA is being executed viz. 10.0, 11.0, 12.0, etc.
Code:
MsgBox Application.Version
PivotTable.Version Property - returns the PivotTable version number, 1, 2, 3, etc.
Code:
MsgBox Worksheets("Sheet1").PivotTables("PivotTable1").Version
