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!

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

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.