Make Pivot Table Macros Run Faster

Pivot table macros can run painfully slowly, even if you have turned off screen updating and disabled events.

To make the code run faster, you can try adding a line that turns off automatic updating in the pivot table. For example:

     Set pt = Worksheets("PivotSales").PivotTables(1)

pt.ManualUpdate = True

At the end of the code, you can reverse the setting, to turn automatic updating on:

     pt.ManualUpdate = False

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

Note: In Excel 2007, the macro won’t run if the Defer Layout Update setting is checked. This problem is fixed in Excel 2010.

____________

2 thoughts on “Make Pivot Table Macros Run Faster”

Gabriel Vega says:

February 17, 2012 at 1:04 pm

I need to update multiple pivot tables in three different books. Currently, when using some VBA code PivotTable field or lose its configuration.

I currently use another book to do this which contains the VBA code for the other three books should not have any code as it is sent by mail.

How I can do?

I hope I can help.

Thank you.

Greetings

Reply
Paul says:

October 27, 2015 at 9:00 pm

I have a report with 2 Top 10 pivot tables sitting down the page, and am writing a macro to change the Top 10 filter field in each.

I’ve tried using the .ManualUpdate = True command, but when I clear the filter on the 1st pivot, I still get an error saying a pivot can’t overlap another (as there are numerous other data rows beyond the top 10).
This suggests the ManualUpdate doesn’t prevent the non-top 10 rows in the pivot from being shown.

I’m hoping you have a tip on how I can do to fix this pls? Thx

Reply