Help with code to remove Sum of from Pivot headers

[Serafin54]

Hi,

I have a code that does remove Sum of from the header but only "Sum of" I can't for the life of me figure out what i'm missing to also remove the space that follows "of" I messed with the pf.Cation 6 and -6 as well as adding a space in the quotes as i do. I can remove less or more but cannot remove that one space. any help is greatly appreciated to point me in the right direction. Work would frown should i punch a monitor. lol

Sub Trim_Header()
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

 

[Alex Blakenburg]

A pivot table won't let you have an aggregation or calculated column that has exactly the same name as a column heading in the underlying data.

The work around is usually to "add a space" to the field name.
By you trying to remove that space from the pivot field name you are making it exactly the same as underlying data field name.

The only way to remove that leading space is to change something else in the field name eg by adding a trailing space while removing the leading space.

 

[Serafin54]

Thank you Alex.

 

[Alex Blakenburg]

You're welcome. Glad I could help.