Macro turn off screenupdating
If you need to update page numbers, you will have to show the application window before doing the update: With See Page X of Y displays or prints as Page 1 of 1, Page 2 of 2 etc. I hear rumors that you may also need to work with the Range object instead of the Selection object, but I have not tested this nearly enough to give a list because I do almost all of my work with Ranges.
By Michael Alexander As your Excel macros become increasingly robust and complex, you may find that they lose performance.
Following are ten ways to help keep your Excel macros running at their optimum performance level.
Did you know that each time a cell that affects any formula in your spreadsheet is changed or manipulated, Excel recalculates the entire worksheet?
Disabling screen updating saves time and resources, allowing your macro to run a little faster. Screen Updating = False 'Place your macro code here Application.
but in the informal testing I've done, I've found that Visible = False makes Word work roughly 15% faster than Screen Updating = False does on identical tasks.
That's about 1 second for every 7-not a trivial amount if your job runs longer than that. Chief among them is that repagination routines don't work when Word is invisible.
You can add another level of performance boosting by using the Enable Events property to tell Excel to ignore events while your macro runs. Calculation = xl Calculation Automatic Application. Set the Display Page Breaks sheet property to False to hide page breaks. You can give your macros a slight boost by cutting out the middleman and performing a direct copy from one cell to a destination cell.
Set the Enable Events property to False before running your macro. If you want to continue to show page breaks after your macro runs, set the Display Page Breaks sheet property back to True. This alternate code uses the Destination argument to bypass the clipboard and copy the contents of cell A1 directly to cell B1.
This method is about approximately 25 times faster than using the Copy method: When recording macros, you will often manipulate the same object more than once.