Excel VBA - change Pivot Table Captions

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.

Sub ChangeCaption()
    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
        End If
    Next
End Sub

Select allOpen in new window

Query-Captions-Sample-Data.xlsm