How Do I Quickly Change a Pivot Value Field Name with VBA?

There are a couple of ways you could approach it:

Option 1: Get rid of the default “sum of”, “count of” that gets automatically inserted when you’re building a pivot table

Do comment out the irrelevant lines, though! For example, if you do not have count fields or average fields, make sure that those are not executable! Also, if your spreadsheet contains fields that have already been renamed, make sure you include a condition in your loop (e.g. on the pivot field name, for instance) that will exclude them from being processed.

Else you might end up with completely unrecognisable names!

Sub ChangeDefaultPFName()
Dim Wks As Worksheet
Dim PT As PivotTable
Dim PF As PivotField
Dim Title As String
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva, September 2016
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each Wks In ActiveWorkbook.Worksheets
For Each PT In Wks.PivotTables
On Error Resume Next
For Each PF In PT.DataFields
Title = PF.name
'comment out the line(s) that you do not need
PF.name = Mid(Title, 8, Len(Title) - 7) & " " 'removes the "sum of", "max of", "min of"
PF.name = Mid(Title, 10, Len(Title) - 9) & " " 'removes the "count of"
PF.name = Mid(Title, 12, Len(Title) - 11) & " " 'removes the "average of", "product of"
Next PF
Next PT
Next Wks
End Sub

Option 2: Use the name of the field as it was defined in the dataset and add a blank space at the end of it to avoid this error

Sub AddDefaultName()
Dim Wks As Worksheet
Dim PT As PivotTable
Dim PF As PivotField
Dim Title As String
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva, September 2016
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each Wks In ActiveWorkbook.Worksheets
For Each PT In Wks.PivotTables
On Error Resume Next
For Each PF In PT.DataFields
Title = PF.SourceName & " "
PF.Caption = Title
Next PF
Next PT
Next Wks
End Sub

While both codes work like a charm, I personally prefer the second one as it is a bit more robust. Regardless of the type of calculation, you need to handle, it will always produce the desired effect. The first VBA snippet, on the other hand, is a bit more volatile and heavily rests on the assumption that all the pivot tables in your spreadsheet use the same type of calculation. Therefore, its usage requires a bit more caution. Last but not least,

that the options stated above will apply the change to all the pivot fields in your workbook. If this shouldn’t be the case, do make sure you apply some if-then statements that will control the execution flow.

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.