How do I not print or count pages? Excel97

R

Rookie 1st class

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
 
T

Tom Ogilvy

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

Rookie 1st class

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 said:
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 said:
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
Click to expand...
Click to expand...
 
T

Tom Ogilvy

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 said:
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 said:
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 said:
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)
Click to expand...
with
zero
to 6 pages depending on survey time and number of thermocouples. How
Click to expand...
do I
not
print unused sheets? How do I adjust "Pages" to not count sheets
Click to expand...
without
an
entry in Range("B15")?
This prints blank pages for unused sheets and is 5 pages minimum.
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
R

Rookie 1st class

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 said:
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 said:
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 said:
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

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
Click to expand...
Click to expand...
Click to expand...
 
R

Rookie 1st class

Let's forget this one Tom... would like your help with Pages Count; Plan B
though.

Rookie 1st class said:
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 said:
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 said:
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.

:

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

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
Click to expand...
Click to expand...
Click to expand...
 

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

How do I repeat a column when printing in a macro? 2
Print specific pages of a Database 2
Count pages in Section and print just those pages 3
Pages Count; Plan B 6
Print Code not working 1
Macro to print several worksheets does not work! 1
How do I add the ability to select the number of printed pages to aDialog Sheet 2
how do i print some pages in color 1

Top