How to Speed Up Macros in Pivots

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/excel-macros-and-vba/speed-up-macros-in-pivots.html
SHARE




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

Please follow and like us:
0


Leave a Reply

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

To avoid automated spam,Please enter the value *

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>