Sub FixAllPivotTables()

Dim pt As PivotTable
Dim ws As Worksheet

If MsgBox("This script is mostly harmless.  It will add an 'x' to the end of every Pivot Table name to stop an Excel bug where sometimes Pivot Charts lose their connection to their original pivot.  Pivot Charts that are visible work with just changing the name in code, but hidden ones lose their source data.  So, this activates each sheet and then zooms to all.  I assume it might break if your chart and your pivot table aren't on the same page?  USE WITH CAUTION! Click 'Cancel' to quit gracefully without messing with anything.", vbOKCancel) = vbOK Then

'change the settings
For Each ws In ActiveWorkbook.Worksheets
'Pivot Charts that are visible work with just changing the name in code, but hidden ones lose their source data.  So, this activates each sheet and then zooms to all.  I assume it might break if your chart and your pivot table aren't on the same page?  USE WITH CAUTION!
ws.Activate
Cells.Select
ActiveWindow.Zoom = True
  For Each pt In ws.PivotTables
    'This one changes the last character of the pivot name to append an "x" so that there are no issues
    'with pivot charts losing their reference when there are >10 pivot tables.
    If Right(pt.Name, 1) <> "x" Then
        pt.Name = pt.Name + "x"
        Debug.Print pt.Name + " I added an x"
    Else
        Debug.Print pt.Name + " had an x already"
    End If
  Next pt
ActiveWindow.Zoom = 100
Range("a1").Select
Next ws


MsgBox "Added an 'x' to the end of each pivot table name if it didn't have one already.", vbOKOnly


Else
MsgBox "Cancelled", vbOKOnly
End If

End Sub