Hi Roger,

Am Sun, 29 Dec 2013 06:27:28 -0800 (PST) schrieb RogerH72:

However the labels in the pivot still implicate the wrong aggregation, namely sum of item in stead of average of item.

Is there any way to get the correct labels in the pivots using VBA?

try:

Sub Test()
Dim wsh As Worksheet
Dim pt As PivotTable
Dim ptf As PivotField

For Each wsh In Worksheets
For Each pt In wsh.PivotTables
For Each ptf In pt.DataFields
ptf.Function = xlAverage
ptf.Caption = Replace(ptf.Caption, "Sum", "Average")
Next
Next
Next
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2