MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Printing Several Ranges with one Macro

Posted by Aaron on December 27, 2001 1:09 PM

I am new to VB and tring to figure out how to go from Lotus macros to VB macros, is getting more difficult than I anticipated. In Lotus I could set up three print ranges, and the Print using a macro looking at those ranges. Is there an easier way to do this in Excel97 aside from using the Macro Recorder? Thanks for any help you are willing to offer.

Posted by Scott on December 27, 2001 1:15 PM

You can use this:

ActiveSheet.PageSetup.PrintArea = "A1:M10,A20:M30,A40:M50"

Just add in the ranges that you want, and seperate by a comma ",".

Posted by Aaron on December 27, 2001 2:28 PM

What if the Ranges are on different Sheets? Do I have to Activate Each individual sheet?

Posted by Scott on December 28, 2001 6:07 AM


If you do "Sheets("SheetName")Select" in between each "ActiveSheet.PageSetup.PrintArea = "PrintRange"" it will set up ranges on each sheet. If you then would like to print once but have it print from each sheet, you can add this to your code:

Sheets(Array("Sheet1", "Sheet2")).Select

Change out Sheet1 and Sheet2 for the names of your sheet. Add this before your print command.