Format to Print or copy and paste formats?

**hutch@edge.net** · 10-01-2008, 09:32 AM

Greetings, Gurus.

Which is faster in VBA, formatting a sheet to print, or copying the formats from an existing sheet and pasting the formats?

Example: I have a rather long routine in one of my macros that formats the sheet for printing, (adjusting column widths, setting print area, etc.), as well as applying some number formats to some of the cells. Would it be quicker to have a mirror sheet in my "master" workbook, and simply copy and paste those formats to the new sheet?

Thanks in advance.

**dominicb** · 10-01-2008, 09:52 AM

Good afternoon hutch@edge.net

For some reason VBA is somewhat sluggish when it comes to manipulating print settings, the quickest way I've found relies on user intervention : select (programatically) all sheets, then open the print settings window and make any changes. Once the user presses enter (or clicks OK) the settings will be applied to all sheets (in tests I've done) around 92% quicker - a considerable time saving, but it relies on user intervention.

Oddly enough, VBA's predecessor wasn't quite so slouchy performing these types of tasks and can still be accessed. There are a couple of articles here that discuss those techniques :
http://groups.google.com/group/micro...c725d0d5a6b3d0
http://www.mcgimpsey.com/excel/udfs/pagesetup.html

HTH

DominicB

**hutch@edge.net** · 10-01-2008, 10:32 AM

Thanks for the reply, Dominic.

Requiring user input isn't an option, as I have multiple users running this macro, and the less interention the better.

I'll study the possibility of using an Excel 4 macro, and also do some time trials to see if it's any quicker to paste the formats.

Thanks.