Hi Claus,

Thanks for your reply. It worked for me perfectly.

Thanks a lot,

Greetings,

Roger


Op zondag 29 december 2013 16:32:27 UTC+1 schreef Claus Busch:
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