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
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
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.