Mark O'Brien
MrExcel MVP
- Joined
- Feb 15, 2002
- Messages
- 3,530
To answer part 1.
OK, usually using "Select" is a huge giveaway when you're trying to speed up macros. Selecting worksheets can take a second.
In the middle of your code you have:
<pre>
ws.Select
ActiveSheet.PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address
With ActiveSheet.PageSetup </pre>
I would suggest changing these lines to:
<pre>
With ActiveSheet.PageSetup
.PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address </pre>
I've not tested the code and I don't know if you can perform the operations that you want to do without the sheet being active, but this is worth a shot.
HTH
OK, usually using "Select" is a huge giveaway when you're trying to speed up macros. Selecting worksheets can take a second.
In the middle of your code you have:
<pre>
ws.Select
ActiveSheet.PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address
With ActiveSheet.PageSetup </pre>
I would suggest changing these lines to:
<pre>
With ActiveSheet.PageSetup
.PageSetup.PrintArea = Range("A1", Range("H65536").End(xlUp)).Address </pre>
I've not tested the code and I don't know if you can perform the operations that you want to do without the sheet being active, but this is worth a shot.
HTH