How Do I Quickly Change a Pivot Table Summary Function with VBA?

• You’ve got to change a bunch of value fields that have all been automatically added as “Count” instead of “Sum”?

• You need to “average” rather than “sum”?

• You’ve suddenly realized that you should be using “Count Numbers” instead of “Count” in a dozen of your spreadsheet pivot tables?

PF_4.Summary_Functions

Cheer up! You can quickly change any of those by using VBA!

NB! Do be cautious!

The code above will change every field in every pivot table in your active workbook, so if you do not want this you will have to restrict its scope by adding some if-then conditions or case statements

Ready for a bit more complicated example?

Check out the one I devised when a change in the source system data meant that I had to alter a calculation method in my report.

Unlike the code above, this one only modifies the fields that have a certain position within a pivot table!

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.