Faster Pivot Table Refresh in Excel Macros

With a large pivot table, running a macro that refreshes that pivot table might be painfully slow. The usual macro steps, like turning off screen updating, and disabling events, don’t solve this problem. Try adding an extra bit of code to your macro, to see if that makes the refresh go faster.

Pivot Table Refresh Macro

On my Contextures site, there are pivot table refresh tips and a few macros, to help you with refresh problems.

For example, this short macro will refresh the first pivot table on a worksheet, as soon as you activate that sheet.

Private Sub Worksheet_Activate()
   Application.EnableEvents = False
   Me.PivotTables(1).RefreshTable
   Application.EnableEvents = True
End Sub

Make Pivot Refresh Faster

If you run that macro, and the refresh takes a long time, try adding code that turns automatic updating on or off for the pivot table.

In the next section, you can see a revised version of the pivot refresh macro, with 2 lines of code added:

one line before the pivot table refresh, to change the ManualUpdate setting to True
- turns off automatic updating
one line after the pivot table refresh, to change the ManualUpdate setting to False
- turns on automatic updating

You could make a similar change to other macros, where a pivot table is being refreshed

Revised Macro for Pivot Table Refresh

Here is the same pivot table macro, with 2 lines added (shown in bold text).

Private Sub Worksheet_Activate()
  Application.EnableEvents = False
  With Me.PivotTables(1)
    .ManualUpdate = True
    .RefreshTable
    .ManualUpdate = False
  End With
  Application.EnableEvents = True
End Sub

Add Code to Excel Worksheet

The macro, shown above, is an event procedure, and it runs automatically when the pivot table worksheet is activated

   Worksheet_Activate

For this event procedure to work correctly, it is stored in the pivot table’s worksheet code module.

If you aren’t sure how to do that, there are instructions on my Contextures site, for copying VBA code to a worksheet module.

Video: Faster Refresh

In this short video, you can see that the pivot table macro runs much faster when the ManualUpdate setting is changed.

That change ensures that the pivot table does not update automatically while the macro runs.