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, ...

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