Hi all,

I sure could use some help with this one, I've looked for hours and I can't seem to figure this one out.

I want to change the aggregation from sum to average, for a great number of pivots in a worksheet , and I came up with below mentioned code, which actually works perfectly well.

This is the code I'd used:

Dim WS As Excel.Worksheet
Dim PVT As Excel.PivotTable
Dim PVF As Excel.PivotField

For Each WS In ActiveWorkbook.Worksheets
For Each PVT In WS.PivotTables
For Each PVF In PVT.DataFields
PVF.Function = xlAverage
Next PVF
Next PVT
Next WS

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?

Many thanks,

With kind regards,

Roger

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

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