User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

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

PivotTable Print Settings

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

 

 

 

PivotTable Print Settings

 

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