VBA to refresh Pivots

Question

I am using code to refresh Pivots and it works, but im getting stuck with the error handler, it gives me:

object variable with block variable not set

Here is the code I am using:

Sub RefreshAllPivots()

On Error GoTo Errhandler

  Dim pivotTable As pivotTable
  For Each pivotTable In ActiveSheet.PivotTables
    pivotTable.RefreshTable
  Next

Errhandler:

     MsgBox "Error Refreshing " & pivotTable.Name

MsgBox "All Pivots Refreshed"

End Sub

Many thanks

Answer 1

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.