Getting correct labels in Excel pivot

[RogerH72]

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

 

[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.

 

[roger.huijskens]

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: