You can designate the column heading in VBA when you add the data field. The
AddDataField function has the following arguments:
AddDataField(Field As Object, [Caption], [Function])

In this example, I have changed the default "Sum of Hours" caption to "
Hours".

ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Hours"), " Hours", xlSum

If it makes mre sense to change the caption after the pivot table has been
created, you can use something like:

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Sum of Hours").Caption =
_ " Hours"

I hope this helps

Stan Shoemaker
Palo Alto, CA


"mo_in_france" wrote:

Hi ALL


This is my first post, so excuse me for any faux pas on etiquette.

I'm creating various pivot tables by VBA code. For each datafield I
change the name so that the columns would not be too wide, and so that
I don't get the "Sum of " thing.


My methodolgy of changing the name relies on the datafield starting
with 'Somme' (Note that I use French excel). I notice that Excel 02 has
'Somme de ' meaning that my code comes to a halt. I have stuck in a few
lines of 'IF' statements relating to Excel version to remedy this.
However, this is not all encompassing, and doesn't help when I pass
some code to my colleagues not using French excel.


Does there exist a method by whereby I can return the datafield's name
irrelevant of which Excel version (French or otherwise), and based upon
the name of the field being summed (or counted for that matter).
Regards