Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA
This section explains how to access, reference, select or use Pivot Fields and Pivot Items in a Pivot Table, viz. Pivot Field, Column Field, Row Field, Data Field, Page Field, Pivot Items in a Field, Hidden Fields, …
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:
1. To access a Pivot Field, both visible and hidden, use the PivotTable.PivotFields Method
2. To access a column field in a PivotTable, use the PivotTable.ColumnFields Property
3. To access a row field in a PivotTable, use the PivotTable.RowFields Property
4. To access a data field in a PivotTable, use the PivotTable.DataFields Property
5. To access a page field in a PivotTable, use the PivotTable.PageFields Property
6. Reference PivotItem in a PivotField
7. To return hidden fields in a PivotTable, use the PivotTable.HiddenFields Property
8. Select a part of the PivotTable using the PivotTable.PivotSelect Method
——————————————————————————————————————
1. To access a Pivot Field, both visible and hidden, use the PivotTable.PivotFields Method.
Example 1: Refer Image 1, after running below code.
Sub PivotTableFieldsItems1()
Dim PvtTbl As PivotTable
Dim pvtFld As PivotField
Dim strPvtFld As String
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘row 10 mentions all the pivot fields:
For Each pvtFld In PvtTbl.PivotFields
strPvtFld = strPvtFld & “, ” & pvtFld.Name
Next
Worksheets(“Sheet1”).Cells(10, 1) = Mid(strPvtFld, 3)
‘sort the “Country” pivot field in ascending order and color the field label:
PvtTbl.PivotFields(“Country”).DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels
PvtTbl.PivotFields(“Country”).LabelRange.Interior.Color = vbYellow
End Sub
2. To access a column field in a PivotTable, use the PivotTable.ColumnFields Property.
Example 2: Refer Image 2, after running below code.
Sub PivotTableFieldsItems2()
Dim PvtTbl As PivotTable
Dim pvtFld As PivotField
Dim strPvtFld As String
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘row 10 mentions all the column fields:
For Each pvtFld In PvtTbl.ColumnFields
strPvtFld = strPvtFld & “, ” & pvtFld.Name
Next
Worksheets(“Sheet1”).Cells(10, 1) = Mid(strPvtFld, 3)
‘sort the “Country” column field in descending order and color the field label:
PvtTbl.ColumnFields(“Country”).DataRange.Sort Order1:=xlDescending, Type:=xlSortLabels
PvtTbl.ColumnFields(“Country”).LabelRange.Interior.Color = vbYellow
End Sub
3. To access a row field in a PivotTable, use the PivotTable.RowFields Property.
Example 3: Refer Image 3, after running below code.
Sub PivotTableFieldsItems3()
Dim PvtTbl As PivotTable
Dim pvtFld As PivotField
Dim strPvtFld As String
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘row 15 mentions all the row fields:
For Each pvtFld In PvtTbl.RowFields
strPvtFld = strPvtFld & “, ” & pvtFld.Name
Next
Worksheets(“Sheet1”).Cells(15, 1) = Mid(strPvtFld, 3)
‘sort the “Car Models” row field in descending order and color the field label:
PvtTbl.RowFields(“Car Models”).DataRange.Sort Order1:=xlDescending, Type:=xlSortLabels
PvtTbl.RowFields(“Car Models”).LabelRange.Interior.Color = vbYellow
End Sub
4. To access a data field in a PivotTable, use the PivotTable.DataFields Property.
Example 4: Refer Image 4, after running below code.
Sub PivotTableFieldsItems4()
Dim PvtTbl As PivotTable
Dim pvtFld As PivotField
Dim strPvtFld As String
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘row 16 mentions all the data fields:
For Each pvtFld In PvtTbl.DataFields
strPvtFld = strPvtFld & “, “ & pvtFld.Name
Next
Worksheets(“Sheet1”).Cells(16, 1) = Mid(strPvtFld, 3)
‘sort the “Sum of Sales” data field in descending order and color the field label:
Set rngKey1 = PvtTbl.DataFields(“Sum of Sales”).DataRange.Cells(1)
PvtTbl.DataFields(“Sum of Sales”).DataRange.Sort key1:=rngKey1, Order1:=xlDescending, Type:=xlSortValues
PvtTbl.DataFields(“Sum of Sales”).LabelRange.Interior.Color = vbYellow
End Sub
5. To access a page field in a PivotTable, use the PivotTable.PageFields Property.
Example 5: Refer Image 5, after running below code.
Sub PivotTableFieldsItems5()
Dim PvtTbl As PivotTable
Dim pvtFld As PivotField
Dim strPvtFld As String
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘row 13 mentions all the page fields:
For Each pvtFld In PvtTbl.PageFields
strPvtFld = strPvtFld & “, “ & pvtFld.Name
Next
Worksheets(“Sheet1”).Cells(13, 1) = Mid(strPvtFld, 3)
‘color the “Year” page field label:
PvtTbl.PageFields(“Year”).LabelRange.Interior.Color = vbYellow
End Sub
6. Reference PivotItem in a PivotField
Example 6a – Hide a specific item in a PivotField:
Sub PivotTableFieldsItems6a()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
PvtTbl.PivotFields(“City”).PivotItems(“London”).Visible = False
End Sub
Example 6b – Show all items in a Pivot Field:
Sub PivotTableFieldsItems6b()
Dim PvtTbl As PivotTable
Dim pvtItm As PivotItem
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
For Each pvtItm In PvtTbl.PivotFields(“City”).PivotItems
pvtItm.Visible = True
Next
End Sub
Example 6c – Loop through all items in a PivotTable Field to hide or show them:
Sub PivotTableFieldsItems6c()
Dim PvtTbl As PivotTable
Dim pvtItm As PivotItem
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
For Each pvtItm In PvtTbl.PivotFields(“City”).PivotItems
If MsgBox(“Hide Item ” & pvtItm & “?”, vbYesNo) = vbYes Then
pvtItm.Visible = False
End If
Next
End Sub
7. To return hidden fields in a PivotTable, use the PivotTable.HiddenFields Property.
Example 7: Refer Image 6, after running below code.
Sub PivotTableAccessFields7()
Dim PvtTbl As PivotTable
Dim pvtFld As PivotField
Dim strPvtFld As String
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘row 15 mentions all the hidden fields:
For Each pvtFld In PvtTbl.HiddenFields
strPvtFld = strPvtFld & “, “ & pvtFld.Name
Next
Worksheets(“Sheet1”).Cells(15, 1) = Mid(strPvtFld, 3)
End Sub
8. Select a part of the PivotTable using the PivotTable.PivotSelect Method.
This method has 3 arguments – Name, Mode & UseStandardName. Name argument is mandatory to specify while other arguments are optional. Name is the PivotTable part to be selected. Mode specifies the PivotTable items to be selected and can be: xlBlanks, xlButton, xlDataAndLabel, xlDataOnly, xlFirstRow, xlLabelOnly and xlOrigin. Default Mode is xlDataAndLabel.
Example 8a: Selecting the Data & Labels for all the items in the “Region” Field – refer Image 7a.
Sub PivotTableAccessFields8a()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
Worksheets(“Sheet1”).Activate
PvtTbl.PivotSelect Name:=“Region”, Mode:=xlDataAndLabel, UseStandardName:=True
‘or
‘PvtTbl.PivotSelect Name:=“Region[All]”, Mode:=xlDataAndLabel, UseStandardName:=True
Selection.Interior.Color = vbYellow
End Sub
Example 8b: Selecting the Data & Labels in the Subtotal row for all the items in the “Region” Field – refer Image 7b.
Sub PivotTableAccessFields8b()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
Worksheets(“Sheet1”).Activate
PvtTbl.PivotSelect “Region[All;Total]”, xlDataAndLabel, True
Selection.Interior.Color = vbYellow
End Sub
Example 8c: Selecting the Data & Labels in the Subtotal row for the “Europe” item only in the “Region” Field – refer Image 7c.
Sub PivotTableAccessFields8c()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
Worksheets(“Sheet1”).Activate
PvtTbl.PivotSelect “Region[Europe;Total]”, xlDataAndLabel, True
Selection.Interior.Color = vbYellow
End Sub
Example 8d: Selecting the Data & Labels for the “Europe” item only in the “Region” Field – refer Image 7d.
Sub PivotTableAccessFields8d()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
Worksheets(“Sheet1”).Activate
PvtTbl.PivotSelect “Region[Europe]”, xlDataAndLabel, True
Selection.Interior.Color = vbYellow
End Sub
Example 8e: Selecting the Labels for the “Europe” item only in the “Region” Field – refer Image 7e.
Sub PivotTableAccessFields8e()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
Worksheets(“Sheet1”).Activate
PvtTbl.PivotSelect “Region[Europe]”, xlLabelOnly, True
Selection.Interior.Color = vbYellow
End Sub
Example 8f: Selecting the complete PivotTable – refer Image 7f.
Sub PivotTableAccessFields8f()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
Worksheets(“Sheet1”).Activate
PvtTbl.PivotSelect Name:=“”, Mode:=xlDataAndLabel, UseStandardName:=True
Selection.Interior.Color = vbYellow
End Sub
Example 8g: Select Grand Totals of Rows – refer Image 7g.
Sub PivotTableAccessFields8g()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
Worksheets(“Sheet1”).Activate
PvtTbl.PivotSelect Name:=“Row Grand Total”, Mode:=xlDataAndLabel, UseStandardName:=True
Selection.Interior.Color = vbYellow
End Sub
Example 8h: Select Grand Totals of Columns – refer Image 7h.
Sub PivotTableAccessFields8h()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
Worksheets(“Sheet1”).Activate
PvtTbl.PivotSelect Name:=“Column Grand Total”, Mode:=xlDataAndLabel, UseStandardName:=True
Selection.Interior.Color = vbYellow
End Sub