Macro runtime
Posted by Kristina Hanson on January 24, 2002 12:08 PM
I have created a macro that copies data from one worksheet and pastes it to 6 other worksheets within the same workbook, depending on criteria. This macro runs within a few seconds on my machine and several others. On some machines it takes up to 20 minutes. On these machines it gets hung up on the Page Setup part of it. Is there any setting in Excel that could be causing this? The machine that takes the longest is the fastest computer.
Thanks in advance for your help.
Kristina Hanson
Posted by Damon Ostrander on January 24, 2002 11:38 PM
Hi Kristina,
The reason that setting PageSetup properties can take much longer on one machine than another has nothing to do with the machine speed. It is because each time you set a PageSetup property (yes, EACH property), Excel checks with the print driver to see if it supports the property setting, and the driver, in turn, polls the printer. This can be very slow, especially if the printer is a network printer on a print server. The best way to improve the speed is to avoid setting properties unnecessarily. If you have recorded the macro PageSetup process, chances are that it is setting numerous properties that are already the default values. ELIMINATE THESE. Sometimes the recorded macro sets 20 PageSetup properties and you can eliminate all but one or two, and you will notice a remarkable improvement in speed. But the underlying problem will not go away, and if you have to set a lot of PageSetup properties the best you can do is to avoid printing from the machines that do the PageSetup slowly.
Damon
