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, ...
1. To access a Pivot Field, both visible and hidden, use the PivotTable.PivotFields Method.
Example 1: Refer Image 1, after running below code.
Image 1
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.
Image 2
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.
Image 3
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.
Image 4
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.
Image 5
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.
Image 6
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.
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.
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.
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.
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.
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.
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.
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.
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