Good idea to disable Automatic calculations in VBA?

[Karthik-Excelsior]

Hi Excel gurus,

I've a huge template where I'm adding a couple macros - basically to copy-paste data across sheets. I realized that since the volume of data in the file is huge, even the macro is taking a lot of time to copy-paste.

But, when I try copy-pasting after disabling the 'Automatic Calculation' feature, it works in a second.

So, my question here is, is it a good idea to disable auto calc before executing this event & turning it on after?

Something like..

ThisWorkbook.Sheets("Summary").EnableCalculation = False

{code}

ThisWorkbook.Sheets("Summary").EnableCalculation = True

what are your thoughts here? Thanks!

 

[Greg Hitchon]

Best way to check if it makes a difference is to try it out...Generally speaking though you are going to have your calculation event fire when you set calculation back to true so it might just be putting it off.

Are the calculations you are doing necessary/what is taking so much time?

 

[taurean]

Does your macro depend on "calculated results" for processing data?

I'm guessing it doesn't as you seem to be satisfied on both counts i.e. macro speed and results you got.

It is common in macros to disable some of the items which may affect the macro execution speed and then enable (without fail) / set them to default at the end of it.

See Kenneth Hobson's entry here which might be useful for you:
VBA Express : Excel - Speed Up Code