conditional printing? macro?

[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

 

[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

 

[Steven]

Thanks Ron,
how could i test this without printing? if possible?

Steve

 

[Ron de Bruin]

Printpreview will not work with From:=1, To:=1

You must print

 

[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....

 

[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.

 

[Ron de Bruin]

Thanks Dave

I never use it like this
I always waste trees<g>

 

[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!)

 

[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

 

[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

 

[Steven]

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

Steve

 

[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

 

[Steven]

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

Many thanks
Steve

 

[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>>

 

[Steven]

maybe it would be easier for you to take a look at the workbook?

Steve