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!