VBA to refresh Pivots

2933 views excel
1

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

answered question

1 Answer

2

The error here is that your code wants to go to the Errhandler even if you do not have an error.

To avoid this you can add Exit Sub before the error handler.

Sub RefreshAllPivots()

On Error GoTo Errhandler

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

Exit Sub 

Errhandler:

     MsgBox "Error Refreshing " & pivotTable.Name

MsgBox "All Pivots Refreshed"

End Sub

posted this

Have an answer?

JD

Please login first before posting an answer.