One of my recent articles elaborated on how to change the name of a Value Pivot Field under the assumption that the change had to be applied on all fields in all the pivot tables in the spreadsheet. It may be very often, however, that you only need to change the names of only 1 or 2 specific fields in all your pivot tables. Then you’ll need to be aware of a couple more pivot table related aspects.
The pivot table position of the field does make a difference, so let’s go through the 4 different options. These are :
- the Pivot Filters section (xlPagefields)
- the Columns section (xlColumnFields)
- the Rows section (xlRowFields)
- the Values section (xlDataFields)
Each of the 4-well-known pivot table squares formes a separate object collection in VBA.
Thanks to these collections, you can also easily re-arrange pivot tables’ layout with VBA
|Dim Wks As Worksheet|
|Dim PT As PivotTable|
|Dim PF As PivotField|
|'written by Angelina Teneva, Sept 2016|
|For Each Wks In ActiveWorkbook.Worksheets|
|For Each PT In Wks.PivotTables|
|For Each PF In PT.PivotFields|
|Set PF = PT.PivotFields("Country")|
|If PF.Orientation <> xlHidden Then|
|'comment out as needed|
|PF.Orientation = xlPageField 'as ReportFilter|
|PF.Orientation = xlRowField 'as RowField|
|PF.Orientation = xlColumnField 'as ColumnField|
|PF.Orientation = xlDataField 'as Value Field|
They also play a pivotal role when you want to restrict your VBA code to only apply to specific fields within your pivot table.
Take the code below as an example – its first two pivot field loops refer to the DataFields collection, which means that they’ll only process pivot fields that have been added to the Values Area of the table.
The last one refers to the PivotFields collection, which means that will process any field regardless of the pivot section to which it has been added. Thus, this is particularly handy if you’d like to rename fields that have different places in the different pivot tables of the workbook
Of course, you can also restrict to RowFields or ColumnFields, but then you need to make sure that the fields you’d like to rename have been consistently placed in either one of these sections across all the pivot tables that need changing.
Happy VBA coding!