Excel: How to clear all renamed PivotTable labels

1. Rename the column header/name in the source
2. Refresh the pivot. This will drop all forced labels and drop field from the pivot.
3. Rename the column name back to what it was
4. Refresh pivot again
5. Add the field back into your pivot.

This macro will remove all captions from the row labels and column labels in all pivottables in a workbook. This will NOT work with PowerPivot Tables.

Sub FixPivotItemCaptions()
Dim pi As PivotItem
Dim pt As PivotTable
Dim wb As Workbook
Dim ws As Worksheet
Dim pf As PivotField

Set wb = ActiveWorkbook

Application.ScreenUpdating = False
For Each ws In wb.Worksheets
    For Each pt In ws.PivotTables
        For Each pf In pt.PivotFields
            For Each pi In pf.PivotItems
                pi.Caption = pi.SourceName
            Next
        Next
    Next
Next
Application.ScreenUpdating = True
End Sub