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>