Getting correct labels in Excel pivot
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 |
Getting correct labels in Excel pivot
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 |
Getting correct labels in Excel pivot
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 |
All times are GMT +1. The time now is 02:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com