which pivot table is not refreshing

[needexcelhelpnow]

I have an excel workbook with lots and lots of sheets. Each sheet feeds into at least two pivot tables, so I have 100 + pivot tables in the workbook.

When I hit "refresh all" to update all the pivot tables, I’ve getting an error message “One or more field names used in the report are no longer valid. If you’re changing the name of a field in your data source, please type a new name in the field. One or more connections didn't refresh or the refresh operation was cancelled. would you like to refresh the remaining connections? ” . I want to know which particular pivot table is this error for?

Any thoughts?

Thank you much!

 

[steve the fish]

Try running this code. Might need to do it more than once:

Sub pivot()

On Error GoTo errhandler

For Each sh In ThisWorkbook.Worksheets
    For Each pt In sh.PivotTables
        pt.PivotCache.Refresh
    Next
Next

Exit Sub

errhandler:

MsgBox sh.Name & ", " & pt.Name

End Sub

 

[needexcelhelpnow]

You my friend are amazing! Thank you.

Try running this code. Might need to do it more than once:

Sub pivot()

On Error GoTo errhandler

For Each sh In ThisWorkbook.Worksheets
    For Each pt In sh.PivotTables
        pt.PivotCache.Refresh
    Next
Next

Exit Sub

errhandler:

MsgBox sh.Name & ", " & pt.Name

End Sub