I've tried reapplying some code I found here and on the web but can't get it to work for my situation.
I pull data using a query and create a pivot table from that data. The data has fields that need to be converted to common units and that is done by adding columns to the dataset. For example, a query field called "Water0708" is converted and the column is called "Water0708_converted.
I need the column headings in the pivot table to be "Water 0708" etc. Using various string functions I almost get what I want and all the strings are stored in variables. Attached is a spreadsheet with sample data.
Dim pt As PivotTable
Dim col As PivotFields
Dim c As PivotField
Set pt = ActiveSheet.PivotTables("PivotTable2")
Set col = pt.PivotFields
For Each c In col
If InStr(c.Caption, "_converted") > 0 Then
OldCaption = c.Caption
theCaption = "Sum of " & OldCaption
NewCaption = Replace(OldCaption, "_converted", "")
NewCaption = Left(NewCaption, 6) & " " & Left(Right(NewCaption, 4), 2) & Right(NewCaption, 2)
pt.PivotFields(OldCaption).Caption = NewCaption