| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I not print or count pages? Excel97
This ALMOST works. I use File, Print, Entire workbook with a "Page of Pages"
header. It contains a cover page and 4 survey sheets(temperatures) with zero to 6 pages depending on survey time and number of thermocouples. How do I not print unused sheets? How do I adjust "Pages" to not count sheets without an entry in Range("B15")? This prints blank pages for unused sheets and is 5 pages minimum. Actually 2 pages (cover and 1 temperature with 12 T/Cs under 1 hr 16 min) is more common. Private Sub CommandButton2_Click() Range("F9").Select Selection = InputBox(Prompt:="[Type & Enter]" & Chr(13) & "The row number to the left of the Summary Start Time.", Title:="SUMMARY START TIME", Default:=Range("F9")) Range("A15:AY92").Select Selection.HorizontalAlignment = xlCenter If Range("B15") < "" Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of &N" Else ActiveSheet.PageSetup.RightHeader = "" End If If Range("AJ54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$92" ElseIf Range("S54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$92" ElseIf Range("B54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$92" ElseIf Range("AJ15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$53" ElseIf Range("S15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$53" ElseIf Range("B15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$53" Else ActiveSheet.PageSetup.PrintArea = "$P$1:$Q$1" End If n = Range("F9") Cells(n, 1).Select Selection.Name = "TUS4SmmryRw" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Thanks in advance; Lou |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I not print or count pages? Excel97
Excel prints a rectanglar area. To get around that you would have to print
each page on the sheet individually. an alternative would be to use layout sheets where you use formulas or code to place your data in the smallest rectangular area and print that. -- Regards, Tom Ogilvy "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... This ALMOST works. I use File, Print, Entire workbook with a "Page of Pages" header. It contains a cover page and 4 survey sheets(temperatures) with zero to 6 pages depending on survey time and number of thermocouples. How do I not print unused sheets? How do I adjust "Pages" to not count sheets without an entry in Range("B15")? This prints blank pages for unused sheets and is 5 pages minimum. Actually 2 pages (cover and 1 temperature with 12 T/Cs under 1 hr 16 min) is more common. Private Sub CommandButton2_Click() Range("F9").Select Selection = InputBox(Prompt:="[Type & Enter]" & Chr(13) & "The row number to the left of the Summary Start Time.", Title:="SUMMARY START TIME", Default:=Range("F9")) Range("A15:AY92").Select Selection.HorizontalAlignment = xlCenter If Range("B15") < "" Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of &N" Else ActiveSheet.PageSetup.RightHeader = "" End If If Range("AJ54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$92" ElseIf Range("S54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$92" ElseIf Range("B54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$92" ElseIf Range("AJ15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$53" ElseIf Range("S15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$53" ElseIf Range("B15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$53" Else ActiveSheet.PageSetup.PrintArea = "$P$1:$Q$1" End If n = Range("F9") Cells(n, 1).Select Selection.Name = "TUS4SmmryRw" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Thanks in advance; Lou |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I not print or count pages? Excel97
Thanks for the reply Tom but I don't understand your answer.
If Range("AJ54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$92" ElseIf Range("S54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$92" Prints 6 or 4 pages (landscape) respectively. Then 2, 3, 2, and the final ElseIf 1 page. that is GREAT. Else ActiveSheet.PageSetup.PrintArea = "$P$1:$Q$1" Is what I need help with. It prints 2 cells (empty, call me a tree hugger). What I need is a method to not include this page is total page count if Range("B15") = <. Since I'm not wasting paper I really don't care whether the page prints, but if it I don't think it can be excluded from the total page count if it prints. "Tom Ogilvy" wrote: Excel prints a rectanglar area. To get around that you would have to print each page on the sheet individually. an alternative would be to use layout sheets where you use formulas or code to place your data in the smallest rectangular area and print that. -- Regards, Tom Ogilvy "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... This ALMOST works. I use File, Print, Entire workbook with a "Page of Pages" header. It contains a cover page and 4 survey sheets(temperatures) with zero to 6 pages depending on survey time and number of thermocouples. How do I not print unused sheets? How do I adjust "Pages" to not count sheets without an entry in Range("B15")? This prints blank pages for unused sheets and is 5 pages minimum. Actually 2 pages (cover and 1 temperature with 12 T/Cs under 1 hr 16 min) is more common. Private Sub CommandButton2_Click() Range("F9").Select Selection = InputBox(Prompt:="[Type & Enter]" & Chr(13) & "The row number to the left of the Summary Start Time.", Title:="SUMMARY START TIME", Default:=Range("F9")) Range("A15:AY92").Select Selection.HorizontalAlignment = xlCenter If Range("B15") < "" Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of &N" Else ActiveSheet.PageSetup.RightHeader = "" End If If Range("AJ54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$92" ElseIf Range("S54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$92" ElseIf Range("B54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$92" ElseIf Range("AJ15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$53" ElseIf Range("S15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$53" ElseIf Range("B15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$53" Else ActiveSheet.PageSetup.PrintArea = "$P$1:$Q$1" End If n = Range("F9") Cells(n, 1).Select Selection.Name = "TUS4SmmryRw" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Thanks in advance; Lou |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I not print or count pages? Excel97
There isn't anyway to set the printarea to something that will indicate not
to print the worksheet. Perhaps you can just clear the page Else 'ActiveSheet.PageSetup.PrintArea = "$P$1:$Q$1" Activesheet.Range("1:65536").Delete Activesheet.usedrange end if then maybe Excel won't try to print it. (I don't know, haven't tested it in this scenario). Another approach would be to group the pages you actually want to print. -- Regards, Tom Ogilvy "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... Thanks for the reply Tom but I don't understand your answer. If Range("AJ54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$92" ElseIf Range("S54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$92" Prints 6 or 4 pages (landscape) respectively. Then 2, 3, 2, and the final ElseIf 1 page. that is GREAT. Else ActiveSheet.PageSetup.PrintArea = "$P$1:$Q$1" Is what I need help with. It prints 2 cells (empty, call me a tree hugger). What I need is a method to not include this page is total page count if Range("B15") = <. Since I'm not wasting paper I really don't care whether the page prints, but if it I don't think it can be excluded from the total page count if it prints. "Tom Ogilvy" wrote: Excel prints a rectanglar area. To get around that you would have to each page on the sheet individually. an alternative would be to use layout sheets where you use formulas or code to place your data in the smallest rectangular area and print that. -- Regards, Tom Ogilvy "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... This ALMOST works. I use File, Print, Entire workbook with a "Page of Pages" header. It contains a cover page and 4 survey sheets(temperatures) with zero to 6 pages depending on survey time and number of thermocouples. How do I not print unused sheets? How do I adjust "Pages" to not count sheets without an entry in Range("B15")? This prints blank pages for unused sheets and is 5 pages minimum. Actually 2 pages (cover and 1 temperature with 12 T/Cs under 1 hr 16 min) is more common. Private Sub CommandButton2_Click() Range("F9").Select Selection = InputBox(Prompt:="[Type & Enter]" & Chr(13) & "The row number to the left of the Summary Start Time.", Title:="SUMMARY START TIME", Default:=Range("F9")) Range("A15:AY92").Select Selection.HorizontalAlignment = xlCenter If Range("B15") < "" Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of &N" Else ActiveSheet.PageSetup.RightHeader = "" End If If Range("AJ54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$92" ElseIf Range("S54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$92" ElseIf Range("B54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$92" ElseIf Range("AJ15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$53" ElseIf Range("S15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$53" ElseIf Range("B15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$53" Else ActiveSheet.PageSetup.PrintArea = "$P$1:$Q$1" End If n = Range("F9") Cells(n, 1).Select Selection.Name = "TUS4SmmryRw" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Thanks in advance; Lou |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I not print or count pages? Excel97
Thanks again Tom
These are templates that once completed are overwritten and reused as ".xls"es. I'm trying to dummy proof them and this seems to be my last stumbling block. I think I can change "Page of Pages" to "Page of _____s", while not pretty it is functional. Thanks for the attempt. Lou May need additional temperatures on different calibration cycles. I tried hiding P&Q1 before posting question; didn't work. How about code that adds right heading "Page[&Page] of Pages[&Pages]" that doesn't count pages where Range("B15")=""? Ariel Narrow 8 pt I can add a print button to each sheet that calls the print proceedure ([ALL PAGES where Range("B15")""] need help with that too) Public function? In module or "ThisWorkbook"? TIA again Lou "Tom Ogilvy" wrote: There isn't anyway to set the printarea to something that will indicate not to print the worksheet. Perhaps you can just clear the page Else 'ActiveSheet.PageSetup.PrintArea = "$P$1:$Q$1" Activesheet.Range("1:65536").Delete Activesheet.usedrange end if then maybe Excel won't try to print it. (I don't know, haven't tested it in this scenario). Another approach would be to group the pages you actually want to print. -- Regards, Tom Ogilvy "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... Thanks for the reply Tom but I don't understand your answer. If Range("AJ54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$92" ElseIf Range("S54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$92" Prints 6 or 4 pages (landscape) respectively. Then 2, 3, 2, and the final ElseIf 1 page. that is GREAT. Else ActiveSheet.PageSetup.PrintArea = "$P$1:$Q$1" Is what I need help with. It prints 2 cells (empty, call me a tree hugger). What I need is a method to not include this page is total page count if Range("B15") = <. Since I'm not wasting paper I really don't care whether the page prints, but if it I don't think it can be excluded from the total page count if it prints. "Tom Ogilvy" wrote: Excel prints a rectanglar area. To get around that you would have to each page on the sheet individually. an alternative would be to use layout sheets where you use formulas or code to place your data in the smallest rectangular area and print that. -- Regards, Tom Ogilvy "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... This ALMOST works. I use File, Print, Entire workbook with a "Page of Pages" header. It contains a cover page and 4 survey sheets(temperatures) with zero to 6 pages depending on survey time and number of thermocouples. How do I not print unused sheets? How do I adjust "Pages" to not count sheets without an entry in Range("B15")? This prints blank pages for unused sheets and is 5 pages minimum. Actually 2 pages (cover and 1 temperature with 12 T/Cs under 1 hr 16 min) is more common. Private Sub CommandButton2_Click() Range("F9").Select Selection = InputBox(Prompt:="[Type & Enter]" & Chr(13) & "The row number to the left of the Summary Start Time.", Title:="SUMMARY START TIME", Default:=Range("F9")) Range("A15:AY92").Select Selection.HorizontalAlignment = xlCenter If Range("B15") < "" Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of &N" Else ActiveSheet.PageSetup.RightHeader = "" End If If Range("AJ54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$92" ElseIf Range("S54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$92" ElseIf Range("B54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$92" ElseIf Range("AJ15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$53" ElseIf Range("S15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$53" ElseIf Range("B15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$53" Else ActiveSheet.PageSetup.PrintArea = "$P$1:$Q$1" End If n = Range("F9") Cells(n, 1).Select Selection.Name = "TUS4SmmryRw" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Thanks in advance; Lou |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I not print or count pages? Excel97
Let's forget this one Tom... would like your help with Pages Count; Plan B
though. "Rookie 1st class" wrote: Thanks again Tom These are templates that once completed are overwritten and reused as ".xls"es. I'm trying to dummy proof them and this seems to be my last stumbling block. I think I can change "Page of Pages" to "Page of _____s", while not pretty it is functional. Thanks for the attempt. Lou May need additional temperatures on different calibration cycles. I tried hiding P&Q1 before posting question; didn't work. How about code that adds right heading "Page[&Page] of Pages[&Pages]" that doesn't count pages where Range("B15")=""? Ariel Narrow 8 pt I can add a print button to each sheet that calls the print proceedure ([ALL PAGES where Range("B15")""] need help with that too) Public function? In module or "ThisWorkbook"? TIA again Lou "Tom Ogilvy" wrote: There isn't anyway to set the printarea to something that will indicate not to print the worksheet. Perhaps you can just clear the page Else 'ActiveSheet.PageSetup.PrintArea = "$P$1:$Q$1" Activesheet.Range("1:65536").Delete Activesheet.usedrange end if then maybe Excel won't try to print it. (I don't know, haven't tested it in this scenario). Another approach would be to group the pages you actually want to print. -- Regards, Tom Ogilvy "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... Thanks for the reply Tom but I don't understand your answer. If Range("AJ54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$92" ElseIf Range("S54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$92" Prints 6 or 4 pages (landscape) respectively. Then 2, 3, 2, and the final ElseIf 1 page. that is GREAT. Else ActiveSheet.PageSetup.PrintArea = "$P$1:$Q$1" Is what I need help with. It prints 2 cells (empty, call me a tree hugger). What I need is a method to not include this page is total page count if Range("B15") = <. Since I'm not wasting paper I really don't care whether the page prints, but if it I don't think it can be excluded from the total page count if it prints. "Tom Ogilvy" wrote: Excel prints a rectanglar area. To get around that you would have to each page on the sheet individually. an alternative would be to use layout sheets where you use formulas or code to place your data in the smallest rectangular area and print that. -- Regards, Tom Ogilvy "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... This ALMOST works. I use File, Print, Entire workbook with a "Page of Pages" header. It contains a cover page and 4 survey sheets(temperatures) with zero to 6 pages depending on survey time and number of thermocouples. How do I not print unused sheets? How do I adjust "Pages" to not count sheets without an entry in Range("B15")? This prints blank pages for unused sheets and is 5 pages minimum. Actually 2 pages (cover and 1 temperature with 12 T/Cs under 1 hr 16 min) is more common. Private Sub CommandButton2_Click() Range("F9").Select Selection = InputBox(Prompt:="[Type & Enter]" & Chr(13) & "The row number to the left of the Summary Start Time.", Title:="SUMMARY START TIME", Default:=Range("F9")) Range("A15:AY92").Select Selection.HorizontalAlignment = xlCenter If Range("B15") < "" Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of &N" Else ActiveSheet.PageSetup.RightHeader = "" End If If Range("AJ54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$92" ElseIf Range("S54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$92" ElseIf Range("B54") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$92" ElseIf Range("AJ15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AY$53" ElseIf Range("S15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$53" ElseIf Range("B15") < "" Then ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$53" Else ActiveSheet.PageSetup.PrintArea = "$P$1:$Q$1" End If n = Range("F9") Cells(n, 1).Select Selection.Name = "TUS4SmmryRw" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Thanks in advance; Lou |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Number of pages in worksheet doesn't match Print Preview pages | Excel Discussion (Misc queries) | |||
| how do i print pages of pages example (1of3) | Excel Discussion (Misc queries) | |||
| Print Area ranges print on separate pages? | Excel Discussion (Misc queries) | |||
| How to print odd pages and even pages seperately in Excel. | Excel Discussion (Misc queries) | |||
| Can I print only specified pages from a print range such as pages 1,3,4,6 | Excel Programming | |||
Linear Mode
