Print a certain area given a situation

[jalba]

I wish to create the following situation:

If A1 = "screen", then let let range b1-d5, as well as e1-g5, be printed.

If A1 = "0", then let only range e1-g5 be printed.

Any suggestions?

***knew i could just come up with some weird stuff***

[Mordred]

Hi jalba, this will get you part way there

Please Login or Register  to view this content.

This will go into the "ThisWorkbook" and then add the following to a regular module

Please Login or Register  to view this content.

[Mordred]

I don't know if this works because I am at home and I don't want to print a bunch of copies. I think that it may be cancelling the print area before the print actually begins. Not sure.

[jalba]

uhhh....ok. really dumb question and advice needed.

So I go into Visual Basic <under "developer" tab>, and i paste in the code <the first batch>, but ummm...how to get it to run? I'm a virgin to macro programming.

[Mordred]

Hi jalba, no worries, you are not asking a dumb question.
Firstly, you need to go into your editor, you can do this by pressing Alt + F11. Once there put the following code into "ThisWorkbook" located in the Project Explorer (usually on the left side of the screen)

Please Login or Register  to view this content.

Next, create a new module by clicking on Insert and then selecting Module. After the new module opens up, paste the following code into it

Please Login or Register  to view this content.

This code will run everytime someone either prints or selects Print Preview. If you have more questions just ask.

Regards:

[Mordred]

Please note that the code I provided to you is only good for sheets(1). If you want it for any sheet then you would have to change it to

Please Login or Register  to view this content.

[jalba]

ok. entered it, and i got the desired result. However, e1 - g5 didn't get printed on a second page (i looked back on my first post, and i realize I didn't mention that criteria...OOPS!!!)

Another mention that i JUST thought of.......let the situation be that the range e1-g5 is located on another sheet <sheet 2>, & range b1-d1 is located on <sheet 1>

PS: thanks for tolerating me.

[Mordred]

Just to be clear, if A1 = screen, print b1:d1 from first sheet and print e1:g5 from the secon sheet.
if A1 = 0 then print e1:g5 of the second page.

Is that right?

[Mordred]

Hi Jalba, please see the attached file and let me know if this is what you want.

[jalba]

if a1 = 0, it must ONLY print out b1:d1, but that's more or less the layout.

I also saw the attached file, and u have it laid out more or less correct. It's now to set it up so that if the result is true, both sheets print out; the other result result being just the first sheet print out.

[Mordred]

Replace the Else statement in the workbook before print sub to

Please Login or Register  to view this content.

and I think that may it. Let me know.

[jalba]

I pasted what you wrote up, but it didn't work.

Did i do something wrong? see attached.

[Mordred]

Hi Jalba, you didn't use the code I supplied to you in the JalbaTest.xls file that I uploaded. I have re-uploaded the file you uploaded. Let me know if it is working for you.

[jalba]

nope. it didn't work.

I went into the Print Preview, and everything looks the same.

[Mordred]

I don't know what else to do for you because it works for me on both Excel 2003 and 2010. I gave it one more go, if it doesn't work, someone else will have to attempt this.

[jalba]

hmmm....i run excel 2007.

Did you see the desired result in the preview, or did u print it out?

[jalba]

hmmm...i use 2007.

U saw the result in the Preview?

[Mordred]

Hi jalba, I did indeed see the results in the preview as well as when I printed. I've asked for help from the gurus on this forum so maybe it will get resolved by one of them. I don't know what else to do, sorry.

[jalba]

Thanks anyway. It's weird though how the result can't be seen in the preview.

What's the deal with the color index though?

[Mordred]

The color index was added because you had it. If it is not necessary then it can be easily removed

[Whizbang]

In 2007 and 2010, the BeforePrint event is not triggered by the "Print Preview and Print" (File->Print) option. Instead you must use the "Print Preview Fullscreen" option.

This link gives you details:
http://support.microsoft.com/kb/982775

[jalba]

yeh, but whizbang, when i printed it out <after i typed in "screen" in a1"), sheet 1 was the only one that printed out, and a1 got printed out on the same sheet.

[Mordred]

yeh, but whizbang, when i printed it out <after i typed in "screen" in a1"), sheet 1 was the only one that printed out, and a1 got printed out on the same sheet.

It is for that reason that I pooled the data into sheet 3 based on sheet 1 - range A1's criteria. I couldn't get it to work anyother way when more than one sheet is involved in setting the print area.

[jalba]

hmmm....okkk. Right now teaching myself how to understand the code.

So at the mean time, I'll come up with an alternative, but i think i got the code wrong.

I have uploaded a file, where I've manually set up two print areas (each print box will print on a separate page). I still want to run the code, given A1 = yes.

[Whizbang]

Try this (Note, this is for the 2-sheet approach):

Please Login or Register  to view this content.

If you want both ranges on a single sheet, but seperate pages, then things are much simpler:

Please Login or Register  to view this content.

[Whizbang]

Oops. The above code (the single sheet option), only works if the two ranges are non-adjacent. So B1:D5,E1:G5 will print on the same page. B1:D5, F1:H5 will print on two pages. Make sense?

[jalba]

closer to the result indeed Whizbang!!!

But Sheet 1 is the one that will always be printed; sheet 2 is the optional.

Alos, why did you duplicate the following line...

Sheets(2).PageSetup.PrintArea = "E1:G5"

Just out of curiosity.

[jalba]

btw whizbang, when i ran that code, the stuff came out on 2 sheets

[jalba]

hey Whizbang, I amended it. See how it works. I did get the desired result.

[Whizbang]

Alos, why did you duplicate the following line...

Sheets(2).PageSetup.PrintArea = "E1:G5"

Just out of curiosity.

You must've viewed my post before I edited it to take that out. I originally had that bit at the bottom, but decided to move it to the top and forgot to clean up. Please take another look at my last code and mark any differences from before.

I do that a lot. I'll notice an error, or find a better way, and I'll edit my post if no one has responded yet.

Sorry for the confusion on that.

[Mordred]

I am at a frustrating loss with this. When I tried the newly revamped workbook, it doesn't print from sheet 2 when the A1 criteria = yes. But yet the code I worked out does print it. How can this new one work for you two but not for me and my way work for me but not for you? I don't understand this at all but I am glad it is worked out.

[Whizbang]

Are you using "yes" or "Yes"? Capitalization does matter in this case.

Mind attaching your workbook? I love little quirky oddities like this... If I can solve them that is.

[Mordred]

Hi Whizbang, if you check out my upload from post # 15 (page 1) and print it does what I thought was needed. However, the criteria in A1 was originally "screen" and 0. I must have been on a path of misunderstanding regarding this.

[Whizbang]

No, the OP just recently changed the criteria in A1 to be yes instead of screen. See this post:http://www.excelforum.com/2546353-post24.html

[Mordred]

I saw that but have you tried what I came up with from post 15? Is it wrong? Am I way off of the desired results?

[Whizbang]

Your workbook seems to have met the criteria as far as I can tell, once you get past the fact that in 2007 and 2010 print preview doesn't trigger the BeforePrint event. But, yeah, it worked fine for me.

[jalba]

Well, I ran the code, and it is working like a charm.

But what if I want to apply a macro to EACH sheet?

I have attached a file, where I've got two sheets within.

On sheet 1, I want to create a situation where if A1 = 1, then ranges B1:B3 and D1:D3 are printed, each range on a separate sheet; the latter resulting in range B1:B3 to be printed ONLY.

On sheet 3 however, I want to create a situation where if A1 = 1, then ranges B2:D2 and B5:D5 are printed, each range on a separate sheet; the latter resulting in range B2:D2 to be printed ONLY.

Using your codes (& my limited experience), I kinda patched up a code, which I pasted under Sheet1 module. Obviously, it didn't work.

Soooo what did i label bad?

[Whizbang]

Does this do what you need?

Please Login or Register  to view this content.

[jalba]

Yup!!! Worked like a chram.

I now noticed in my previous post that the attachment didn't upload. Sorry bout that Whizbang.

My query is, why can't you write code under "sheet1 (sheet1)"? Do all codes usually be restricted to "ThisWorkbook"?

[Whizbang]

You can write code under various sheet modules, but the "BeforePrint" event occurs at the ThisWorkbook level.

[jalba]

Dammit!!!

I entered the code Whizbang sent me (with a few modifications), and I am not getting the bloody result!!!

Can you see if it works for you?

[Whizbang]

The file you uploaded is a .xlsx file, which means no macros are part of it. Please re-upload your file (as an xlsm) or give me the printing criteria and I will generate a code based on the file provided.

[jalba]

Here you go Whizbang.

[Whizbang]

Your Sash sheet name has a space at the end.

"Sash " does not equal "Sash" and so your page setup will not change.

[jalba]

DOH!!!

Me sooo stoopid...lolz.
Cool man, got it. Thanks for ALL your help.

[jalba]

A while ago, based on the help I got here, I had written a code where if B26 = "YES", two pages are to print, but one if the answer is "NO"

Recentyl I had added an extra row, causing the cell to move from B26 to B27.

I amended my VBA from B26 to B27, but now it's not working the way I want it.

Any help?