How do I quickly change the Pivot Field Name of Only Specific Fields?

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

Sub ShowFieldinPT()
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
End If
Next PF
Next PT
Next Wks
End Sub

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

Sub ChangePFCaptionOfCertainFields()
Dim Wks As Worksheet
Dim PT As PivotTable
Dim PF As PivotField
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva, Oct 2016
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each Wks In ActiveWorkbook.Worksheets
For Each PT In Wks.PivotTables
'replace part of the name of a data field
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each PF In PT.DataFields
'inserts blank between a currency symbol and the text
If Left(PF.Caption, 1) = Chr(128) Then PF.name = Chr(128) & Chr(32) & Right(PF.name, Len(PF.name) - 1)
'replace pound with euro
If PF.Caption Like "*£*" Then PF.name = Chr(128) & Chr(32) & Right(PF.name, Len(PF.name) - 1)
Next PF
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'completely change the name of a data field
For Each PF In PT.DataFields
If PF.Caption Like "*USD*" Then PF.name = "AUD"
Next PF
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'change the name of a column, row or page field
For Each PF In PT.PivotFields
If PF.Orientation <> xlHidden And PF.Orientation <> xlDataField Then
If PF.Caption Like "*Country*" Then PF.name = "User Country"
End If
Next PF
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Next PT
Next Wks
End Sub

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!