How to Speed Up Macros in Pivots

How to improve the speed of VBA macro code?

In this article, we will learn how to run macros faster with pivots, using VBA code.

Question): I am working on dashboard which has significant number of pivot tables & pivot charts are made. The problem I am facing is when I refresh all the pivots then the whole system hangs. I want a magic piece of code that will help me.

We need to follow the below steps:

Click on Developer tab
From Code group, select Visual Basic

Enter the following code in the standard module

Sub FastMacro()

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

ActiveWorkbook.RefreshAll

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

End Sub

The above screenshot contains calculation mode in manual mode & screen updating to be false initially before refreshing all the pivots & then calculation mode to be automatic & screen updating to be true.

If you follow the above approach then this will result in significant updation as compared to do a simple refresh of pivots.