conditional printing? macro?

  • Thread starter Thread starter Steven
  • Start date Start date
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
 
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
 
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....
 
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.
 
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!)
 
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
 
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
 
Dave could you explain what this code does please because im not sure if it
does everything i would like, many thanks.

Steve
 
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
Click to expand...
 
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
Click to expand...
<<snipped>>
 
Back
Top