VBA to refresh Pivots
You only want to display the error message if there is an error. Additionally, you may wish to check if the error happened while the pivotTable object was assigned:
Sub RefreshAllPivots()
On Error GoTo ErrHandler
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt
ErrHandler:
If err Then
If Not pt Is Nothing Then
MsgBox "Error Refreshing " & pt.Name
Else
MsgBox "Unexpected error"
End If
Else
MsgBox "All Pivots Refreshed"
End If
End Sub
Note that I renamed your pivotTable
variable to pt
- it's not great practice to use reserved words as variable names.