55 votes12 comments · Excel for Windows (Desktop Application) » PivotTables and Power Pivot · Flag idea as inappropriate… · Admin →
This is something that clearly needs improvement, and we’re looking at it now. Please keep the votes coming as a way to help us prioritize this work. Thanks!
An error occurred while saving the commentGuillermo J Wainselboim commented
Saw this posting, and thought it may help out, try's running an update and gives you the sheets that have errors and which pivot tables are the problem. I tried it and seemed to work, and working to see if by moving pivot tables actually works
' PivotCheck Macro
' Running this macro will refresh all the pivot tables in the workbook. IF there are errors, a window will pop up and tell you which pivot table and what worksheet is causing the error. Why excel does not do this automatically is a mystery. Party on.
Dim pt As PivotTable
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
For Each pt In wks.PivotTables
On Error Resume Next
If Err <> 0 Then MsgBox "pivot table """ & pt.Name & """" & vbCr & _
"refresh error on " & vbCr & "worksheet """ & wks.Name & """"
Set pt = Nothing
Set wks = Nothing