Hi Russ
I am totally unaware of any in built function in XL2003 that would do this,
and there is no such function in XL2007.
However, the following code will achieve what you want.
Sub ChangePTName()
Dim pt As PivotTable, pf As PivotField, ws As Worksheet, i As Long
Application.ScreenUpdating = False
Set ws = ActiveSheet
For i = 1 To ws.PivotTables.Count
Set pt = ws.PivotTables(i)
pt.ManualUpdate = True
For Each pf In pt.DataFields
If pf.Function = xlSum Then
If Left(pf.Caption, 6) = "Sum of" Then
pf.Caption = Right(pf.Caption, Len(pf.Caption) - 6)
End If
End If
Next
pt.ManualUpdate = False
Next i
Application.ScreenUpdating = True
End Sub
Copy the Code above
Alt+F11 to invoke the
VB Editor
InsertModule
Paste code into white pane that appears
Alt+F11 to return to Excel
To use
Select sheet containing the PT's
Alt+F8 to bring up Macros
Highlight ChangePTName
Run
--
Regards
Roger Govier
"Russ" wrote in message
...
In 2003, we had a formatting option that would globally remove €śSum Of€ť
from
the pivot table values. I like that feature. However, I cant seem to
find
the same thing in 2007. I can manually change them, but thats no fun,
especially if I have a bunch of values.
Does anyone have solution to this question?
Thanks