Print

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

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