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

 

 img1

 

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

 

img2

 

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.

 

image 19

Download – How to Speed Up Macros in Pivots – xlsm

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube