conditional printing? macro?

S

Steven

here's my situation, i have a workbook with many sheets in it, each sheet if
the default print shortcut button was used would print to 4 pages of A4
paper.

the data on these sheets varies everyday, someday's only 1 page per sheet
would be full of data others 2, 3, or 4 etc. how could i write a macro that
would be able to print only the needed pages?, each page, if it contains any
data would have a value in a totals cell.

so i would need code along these lines

Sheets("sheet1").Select
IF(OR(total1="",total1=0),"don't print","don't print") ELSE set data1 = TRUE
IF(OR(total2="",total2=0),"don't print","don't print") ELSE set data2 = TRUE
IF(OR(total3="",total3=0),"don't print","don't print") ELSE set data3 = TRUE
IF(OR(total4="",total4=0),"don't print","don't print") ELSE set data4 = TRUE

IF(OR(data1 ="TRUE",data1="FALSE"),"Print Page 1 to 1", "don't print")
IF(OR(data1 ="TRUE",data1="FALSE"),"Print Page 1 to 2", "don't print")
IF(OR(data1 ="TRUE",data1="FALSE"),"Print Page 1 to 3", "don't print")
IF(OR(data1 ="TRUE",data1="FALSE"),"Print Page 1 to 4", "don't print")

Hope this helps to explain what im trying to do, and someone is able to code
something that would work.

Many thanks,
Steve
 
R

Ron de Bruin

Hi Steven

Try this

Sub test()
If Range("total1") > 0 Then ActiveSheet.PrintOut From:=1, To:=1
If Range("total2") > 0 Then ActiveSheet.PrintOut From:=2, To:=2
If Range("total3") > 0 Then ActiveSheet.PrintOut From:=3, To:=3
If Range("total4") > 0 Then ActiveSheet.PrintOut From:=4, To:=4
End Sub
 
S

steveb

How about instead of ActiveSheet.PrintOut set up a way to get the range for
the area to print and return it in a MsgBox?
or DeBug Print? or....
 
D

Dave Peterson

Or maybe:

Option Explicit
Sub test()
If Range("total1") > 0 Then ActiveSheet.PrintOut preview:=True, From:=1, To:=1
If Range("total2") > 0 Then ActiveSheet.PrintOut preview:=True, From:=2, To:=2
If Range("total3") > 0 Then ActiveSheet.PrintOut preview:=True, From:=3, To:=3
If Range("total4") > 0 Then ActiveSheet.PrintOut preview:=True, From:=4, To:=4
End Sub

it worked ok for me in xl2002.
 
D

Dave Peterson

For the most part, I keep my (personal) printer off.

In fact, it's paused (under control panel).

Before I did this, I used all the trees in the Sahara Forest. Well, it's a
desert NOW!

(It's an old joke, but I'm an old man!)
 
S

Steven

what code would i need to use on new commandbutton to do the following:

1) select sheet 1
2) run another commandbutton from within that sheet "Private Sub
CommandButton2_Click()
SortKeys = "EC"
Call SortAllRanges
End Sub"
3) look at "total4" if greater than 0 print pages 1 to 4, 1 copy, goto next
else
look at "total3" if greater than 0 print pages 1 to
3, 1 copy, goto next
else
look at "total2" if greater
than 0 print pages 1 to 2, 1 copy, goto next
else
look at
"total1" if greater than 0 print pages 1 to 1, 1 copy, goto next

else

end if
4) select sheet 2
5) same as 2)
6) same as 3)
7) select sheet 3

i would be very grateful for your help as this would enable me to setup lots
of different auto print options for multiple worksheets.

Steve
 
D

Dave Peterson

One way:

If Range("total4").Value > 0 Then
ActiveSheet.PrintOut preview:=True, From:=4, To:=4
ElseIf Range("total3").Value > 0 Then
ActiveSheet.PrintOut preview:=True, From:=3, To:=3
ElseIf Range("total2").Value > 0 Then
ActiveSheet.PrintOut preview:=True, From:=2, To:=2
ElseIf Range("total1").Value > 0 Then
ActiveSheet.PrintOut preview:=True, From:=1, To:=1
Else
'do nothing
End If
 
S

Steven

Dave could you explain what this code does please because im not sure if it
does everything i would like, many thanks.

Steve
 
D

Dave Peterson

if total4 is bigger than 0, then print
if it's not, then check total3. if that's bigger than 0, then print.
if it's not,....

And so forth.

You may want to test it against a small version of your workbook to see if it
works the way you want.


Dave could you explain what this code does please because im not sure if it
does everything i would like, many thanks.

Steve
 
S

Steven

will it look at all sheets, only active or only ones listed in the macro?

Many thanks
Steve
 
D

Dave Peterson

Depends on what you mean by "look at".

If the total1, ..., total4 ranges are on different sheets, then it'll look at
them (are they worksheet level names or workbook level names?).

But this line:
ActiveSheet.PrintOut preview:=True, ...
means just the activesheet.



will it look at all sheets, only active or only ones listed in the macro?

Many thanks
Steve
<<snipped>>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vlookup reference short cuts 3
Get data from alternated cells 3
Printing hidden worksheets? 4
Run Time Error '13' Type mismatch 2
Printing 2
Need a bit of help on comparing cells 2
autofilter 2
Excel VBA Macro 8

Top