Macros Causing Workbook to Run Very Slow

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a large workbook with many buttons that run different macros. Mostly
these macros copy and paste infomation for backup, analysis, and later use.
I recently added a new feature that forced me to add many more of these
functions, and now when I push a button, the workbook moves unbearably slow.
Is there any way to speed up the running of these macros or is there a
different way I could set up the page so it doesn't have to copy and paste a
million times?

Thanks in Advance,

Adam Bush
 
Without seeing the w/book and associated macros it's not possible to advise
you on what to do to improve performance.
 
Most of the macros literally select a range, copy it, then select a new range
and paste special-values. These are the majority of my macros and the ones
that are slowing down my workbook.

Thanks

Adam Bush
 
I'm not sure how you created your macros (e.g. did you record them or
write them yourself). If you recorded the macro, review the code (hit
ALT+F11) and see if the workbook autosaved while you were recording
the macro. This happened to me once and it took forever for me to find
it, but once I did, the macro went much faster.

As for another way to reorganize the data - you would need to explain
how your data is currently organized before we could comment.
 
If you use SELECT then this can slow things down considerable.

Perhaps an example of your copy/paste code might help.
 
If the cells you are pasting the data into are referenced in formulas, the
slowness could be related to those cells updating (the ones with formulas).
To test this, you can set calculation updating to manual, run the macro and
see if performance improves (TOOLS - OPTIONS - Calculation - choose manual).

If this is the cause, change calculation back to manual, and add the
following to the beginning of your code:

Application.ScreenUpdating = False

This may or may not help.

Ken
 
Here is a simple example of my code:

Range("AB27:AG27").Select
Selection.Copy
Range("AL27").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

I also had another development. Today I ran the program on a different
computer with the same processing speed and my entire funtion, which takes
close to a minute on the other computer, took 2 seconds. Can anyone explain
this?

Thanks

Adam Bush
 
Back
Top