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