Setting Print Area across multiple sheets?

Good evening everybody.

I am working on a spreadsheet that has several sheets that are formatted for printing. I have the print area's set up with named ranges and the formatting looks good.

If the user prints 1 sheet only everything is OK, but I have one macro that I am working on to enable the users to select multiple sheets to print.

I am using the printpreview command so they can check the formatting before the actual print. There are buttons with macros on each tab with code like this:

Please Login or Register  to view this content.

This works great, but I would like to let the user choose multiple areas from different sheets and print it at one time. I have a method in place to select the various ranges, but I can not get them to print.

I tried setting a "Print_All" named range like this:

"Print_All" = "Sheet1_Print","Sheet2_Print","Sheet3_Print"



But i get yelled at by excel because of a global range error (The code is in a Module, not attached directly to one of the sheets)

The idea is that while looking at the preview they could view all pages before confirming that they did want to print.

I also tried something like this

Please Login or Register  to view this content.

But all that it previews is the last page selected (all of them are highlighted however)

Any Ideas? I am sure it is something relatively simple but I am just brain dead right now

Thanks


Lee

Hello Lee,

This should help...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Works like a charm

I appreciate the help

Lee

The code provided works great,

I been racking my brain trying to figure out how to make it so the user can select WHICH sheets they want print.

I have a series of checkboxes that they can select/deselect to print or ignore certain sheets.
I have the checkboxes and the method of figuring out which sheets to print, but I can not figure out how to add a variable number of these sheets to the array to be printed. I did not ask this in the original question because I wanted to figure it out on my own, but I am not having mush luck. VB.net has some variable types such as a Stack where you can add and subtract items from the "array" at will without having a predefined number of items.

Using pseudocode I am looking to do something like this:

Please Login or Register  to view this content.

Thanks again for any suggestions

Lee

Hello Lee,

This macro will set the Print Area to whatever the user has selected on the worksheet, as long as the check box is checked. Your idea presented such an interesting challenge to me that I have added it to my code library.

New Macro code:

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Thank you so much!

I understand the logic for doing this, but I am still learning the syntax for VBA to enable me to do what I want.

I have quite a bit of programming experience in other languages, but I am still on the upward learning curve of VBA. At least the thought process is very similar, just implementing your ideas into actual code takes a little time to learn.


Lee


EDIT: Spelling

Hello Leith,

You're new macro code seemed like a perfect solution to a need I'm surprised more people don't have. However, when I run the macro, I receive a "Type mismatch (Error 13)", Type mismatch. Debug stops at the line containing Sheets(SelWks()).Select. Any ideas?

Thanks in advance.