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

Open in new window

Query-Captions-Sample-Data.xlsm
CarenCAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Try this:
Sub ChangeCaption()
    Dim pt As PivotTable
    Dim col As PivotFields
    Dim c As PivotField
    
    Set pt = ActiveSheet.PivotTables("PivotTable4")
    Set col = pt.DataFields
    For Each c In col
        If InStr(c.Caption, "_converted") > 0 Then
            OldCaption = c.Caption
            NewCaption = Replace(Replace(OldCaption, "_converted", ""), "Sum of ", "")
            NewCaption = Left(NewCaption, 5) & " " & Right(NewCaption, 4)
            c.Caption = NewCaption
        End If
    Next
End Sub

Open in new window

0
 
CarenCAuthor Commented:
That did it . . . thank you for saving me a lot of time!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.