| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional printing? macro?
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 |
|
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional printing? macro?
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 -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message news:kELoc.118$6X5.92@newsfe1-win... 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 |
|
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional printing? macro?
Thanks Ron,
how could i test this without printing? if possible? Steve "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message news:kELoc.118$6X5.92@newsfe1-win... 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 |
|
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional printing? macro?
Printpreview will not work with From:=1, To:=1
You must print -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message ... Thanks Ron, how could i test this without printing? if possible? Steve "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message news:kELoc.118$6X5.92@newsfe1-win... 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 |
|
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional printing? macro?
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.... -- steveb (Remove 'NOSPAM' from email address if replying direct) "Ron de Bruin" wrote in message ... Printpreview will not work with From:=1, To:=1 You must print -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message ... Thanks Ron, how could i test this without printing? if possible? Steve "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message news:kELoc.118$6X5.92@newsfe1-win... 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 |
|
#6
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional printing? macro?
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. steveb wrote: 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.... -- steveb (Remove 'NOSPAM' from email address if replying direct) "Ron de Bruin" wrote in message ... Printpreview will not work with From:=1, To:=1 You must print -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message ... Thanks Ron, how could i test this without printing? if possible? Steve "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message news:kELoc.118$6X5.92@newsfe1-win... 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 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional printing? macro?
Thanks Dave
I never use it like this I always waste trees<g -- Regards Ron de Bruin http://www.rondebruin.nl "Dave Peterson" wrote in message ... 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. steveb wrote: 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.... -- steveb (Remove 'NOSPAM' from email address if replying direct) "Ron de Bruin" wrote in message ... Printpreview will not work with From:=1, To:=1 You must print -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message ... Thanks Ron, how could i test this without printing? if possible? Steve "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message news:kELoc.118$6X5.92@newsfe1-win... 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 -- Dave Peterson |
|
#8
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional printing? macro?
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!) Ron de Bruin wrote: Thanks Dave I never use it like this I always waste trees<g -- Regards Ron de Bruin http://www.rondebruin.nl "Dave Peterson" wrote in message ... 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. steveb wrote: 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.... -- steveb (Remove 'NOSPAM' from email address if replying direct) "Ron de Bruin" wrote in message ... Printpreview will not work with From:=1, To:=1 You must print -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message ... Thanks Ron, how could i test this without printing? if possible? Steve "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message news:kELoc.118$6X5.92@newsfe1-win... 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 -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional printing? macro?
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" wrote in message ... 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. steveb wrote: 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.... -- steveb (Remove 'NOSPAM' from email address if replying direct) "Ron de Bruin" wrote in message ... Printpreview will not work with From:=1, To:=1 You must print -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message ... Thanks Ron, how could i test this without printing? if possible? Steve "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message news:kELoc.118$6X5.92@newsfe1-win... 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 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional printing? macro?
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 wrote: 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" wrote in message ... 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. steveb wrote: 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.... -- steveb (Remove 'NOSPAM' from email address if replying direct) "Ron de Bruin" wrote in message ... Printpreview will not work with From:=1, To:=1 You must print -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message ... Thanks Ron, how could i test this without printing? if possible? Steve "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message news:kELoc.118$6X5.92@newsfe1-win... 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 -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional printing? macro?
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" wrote in message ... 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 wrote: 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" wrote in message ... 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. steveb wrote: 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.... -- steveb (Remove 'NOSPAM' from email address if replying direct) "Ron de Bruin" wrote in message ... Printpreview will not work with From:=1, To:=1 You must print -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message ... Thanks Ron, how could i test this without printing? if possible? Steve "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message news:kELoc.118$6X5.92@newsfe1-win... 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 -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional printing? macro?
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. Steven wrote: 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" wrote in message ... 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 wrote: 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" wrote in message ... 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. steveb wrote: 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.... -- steveb (Remove 'NOSPAM' from email address if replying direct) "Ron de Bruin" wrote in message ... Printpreview will not work with From:=1, To:=1 You must print -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message ... Thanks Ron, how could i test this without printing? if possible? Steve "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message news:kELoc.118$6X5.92@newsfe1-win... 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional printing? macro?
will it look at all sheets, only active or only ones listed in the macro?
Many thanks Steve "Dave Peterson" wrote in message ... 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. Steven wrote: 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" wrote in message ... 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 wrote: 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" wrote in message ... 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. steveb wrote: 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.... -- steveb (Remove 'NOSPAM' from email address if replying direct) "Ron de Bruin" wrote in message ... Printpreview will not work with From:=1, To:=1 You must print -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message ... Thanks Ron, how could i test this without printing? if possible? Steve "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" wrote in message news:kELoc.118$6X5.92@newsfe1-win... 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional printing? macro?
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. Steven wrote: will it look at all sheets, only active or only ones listed in the macro? Many thanks Steve "Dave Peterson" wrote in message ... 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. <<snipped -- Dave Peterson |
|
#15
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional printing? macro?
maybe it would be easier for you to take a look at the workbook?
Steve "Dave Peterson" wrote in message ... 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. Steven wrote: will it look at all sheets, only active or only ones listed in the macro? Many thanks Steve "Dave Peterson" wrote in message ... 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. <<snipped -- Dave Peterson |
|
#16
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional printing? macro?
What happened when you tried it?
Steven wrote: maybe it would be easier for you to take a look at the workbook? Steve "Dave Peterson" wrote in message ... 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. Steven wrote: will it look at all sheets, only active or only ones listed in the macro? Many thanks Steve "Dave Peterson" wrote in message ... 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. <<snipped -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional formatting and printing | Excel Discussion (Misc queries) | |||
| conditional printing | Excel Discussion (Misc queries) | |||
| Conditional Printing | Excel Discussion (Misc queries) | |||
| Printing conditional images | Excel Discussion (Misc queries) | |||
| conditional printing | Excel Programming | |||
Linear Mode
