Before Print - Case Select?
 

In a workbook I have 3 sheets where the number of rows (I wish to print)
varys each month. SO that I wouldn't have to manually set the print range I
thought I'd set up a macro whcih would 1) find the last active row and set
the printarea to:
Pagesetup.PrintArea = "A5:P" & Lrow
My three sheets are MyNum1, MyNum2, Mynum3 say.
I thought I might use the Before Print WB event to run things, but obviously
can have only one Before Print Event, so I thought maybe a Select case
Statement might
work where I have 3 different standard modules, one for each case
My Before Print handler would include the Case Select
With choices PMyNum1, PMyNum2, PMuNum3

The 3 standard modules would be named PMyNum1, PMyNum2, PMuNum3
with the details. Am I proceeding correctly "in my thinking" or am I headed
down the "path of ruin"?

Tks in advance,
Jim May

Before Print - Case Select?
 

Nothing wrong with that, but do you actually need separate modules? Could
you not just test the Activesheet and calculate the last row generically
within that?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Jim May" wrote in message
...
In a workbook I have 3 sheets where the number of rows (I wish to print)
varys each month. SO that I wouldn't have to manually set the print range
I
thought I'd set up a macro whcih would 1) find the last active row and set
the printarea to:
Pagesetup.PrintArea = "A5:P" & Lrow
My three sheets are MyNum1, MyNum2, Mynum3 say.
I thought I might use the Before Print WB event to run things, but
obviously
can have only one Before Print Event, so I thought maybe a Select case
Statement might
work where I have 3 different standard modules, one for each case
My Before Print handler would include the Case Select
With choices PMyNum1, PMyNum2, PMuNum3

The 3 standard modules would be named PMyNum1, PMyNum2, PMuNum3
with the details. Am I proceeding correctly "in my thinking" or am I
headed
down the "path of ruin"?

Tks in advance,
Jim May

Before Print - Case Select?
 

If all your doing is setting the printarea, then I don't see any reason to
have any modules or additional code outside the beforeprint event.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet, Lastrow As Long
For Each sh In ActiveWindow.SelectedSheets
Lastrow = sh.Cells(Rows.Count, "A").End(xlUp)
sh.PageSetup.PrintArea = "'" & sh.Name & _
"'!A5:P" & Lastrow
Next

End Sub

If you have some peculiar printing situation then just process the whole
workbook each time

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet, Lastrow As Long
For Each sh In ThisWorkbook.Worksheets
Lastrow = sh.Cells(Rows.Count, "A").End(xlUp)
sh.PageSetup.PrintArea = "'" & sh.Name & _
"'!A5:P" & Lastrow
Next

End Sub

If you only want to do the 3 sheets, then



Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet, Lastrow As Long
For Each sh In ThisWorkbook.Worksheets
If instr(1,sh.Name,"mynum",vbTextCompare) then
Lastrow = sh.Cells(Rows.Count, "A").End(xlUp)
sh.PageSetup.PrintArea = "'" & sh.Name & _
"'!A5:P" & Lastrow
End if
Next

End Sub

--
Regards,
Tom Ogilvy


"Jim May" wrote:

In a workbook I have 3 sheets where the number of rows (I wish to print)
varys each month. SO that I wouldn't have to manually set the print range I
thought I'd set up a macro whcih would 1) find the last active row and set
the printarea to:
Pagesetup.PrintArea = "A5:P" & Lrow
My three sheets are MyNum1, MyNum2, Mynum3 say.
I thought I might use the Before Print WB event to run things, but obviously
can have only one Before Print Event, so I thought maybe a Select case
Statement might
work where I have 3 different standard modules, one for each case
My Before Print handler would include the Case Select
With choices PMyNum1, PMyNum2, PMuNum3

The 3 standard modules would be named PMyNum1, PMyNum2, PMuNum3
with the details. Am I proceeding correctly "in my thinking" or am I headed
down the "path of ruin"?

Tks in advance,
Jim May

Before Print - Case Select?
 

Thanks Tom for the input; I failed to mention that each sheet also has its own
parculiar Header info (and differing Column stretch) so I ended up doing as
follows:

It seems to work - there are 3 other sheets in the Wb which are static and I
rarely
print, so they are not "registered" in the Before Print Handler. Do you see
any "looming-problems"?
TIA,
Jim May

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ShtName = ActiveSheet.Name
Select Case ShtName
Case Is = "BinderFinal"
Call PrtBinderFinal
Case Is = "Co41ClinicSort"
Call PrtCo41ClinicSort
Case Else
Exit Sub
End Select
End Sub

Sub PrtBinderFinal()
Lrow = Cells(Rows.Count, "B").End(xlUp).Row
With ActiveSheet
.PageSetup.PrintArea = "A5:K" & Lrow
.PageSetup.CenterHeader = "Cash Account " _
& "General Ledger Report " _
& "for Month of " & Format(.Range("C5"), "mmmm - yyyy")
End With
End Sub
Sub PrtCo41ClinicSort()
Lrow = Cells(Rows.Count, "I").End(xlUp).Row
With ActiveSheet
.PageSetup.PrintArea = "A5:O" & Lrow
.PageSetup.CenterHeader = "CMG Cash Accounts " _
& "General Ledger Report " _
& "for Month of " & Format(.Range("C5"), "mmmm - yyyy")
End With
End Sub



"Tom Ogilvy" wrote:

If all your doing is setting the printarea, then I don't see any reason to
have any modules or additional code outside the beforeprint event.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet, Lastrow As Long
For Each sh In ActiveWindow.SelectedSheets
Lastrow = sh.Cells(Rows.Count, "A").End(xlUp)
sh.PageSetup.PrintArea = "'" & sh.Name & _
"'!A5:P" & Lastrow
Next

End Sub

If you have some peculiar printing situation then just process the whole
workbook each time

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet, Lastrow As Long
For Each sh In ThisWorkbook.Worksheets
Lastrow = sh.Cells(Rows.Count, "A").End(xlUp)
sh.PageSetup.PrintArea = "'" & sh.Name & _
"'!A5:P" & Lastrow
Next

End Sub

If you only want to do the 3 sheets, then



Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet, Lastrow As Long
For Each sh In ThisWorkbook.Worksheets
If instr(1,sh.Name,"mynum",vbTextCompare) then
Lastrow = sh.Cells(Rows.Count, "A").End(xlUp)
sh.PageSetup.PrintArea = "'" & sh.Name & _
"'!A5:P" & Lastrow
End if
Next

End Sub

--
Regards,
Tom Ogilvy


"Jim May" wrote:

In a workbook I have 3 sheets where the number of rows (I wish to print)
varys each month. SO that I wouldn't have to manually set the print range I
thought I'd set up a macro whcih would 1) find the last active row and set
the printarea to:
Pagesetup.PrintArea = "A5:P" & Lrow
My three sheets are MyNum1, MyNum2, Mynum3 say.
I thought I might use the Before Print WB event to run things, but obviously
can have only one Before Print Event, so I thought maybe a Select case
Statement might
work where I have 3 different standard modules, one for each case
My Before Print handler would include the Case Select
With choices PMyNum1, PMyNum2, PMuNum3

The 3 standard modules would be named PMyNum1, PMyNum2, PMuNum3
with the details. Am I proceeding correctly "in my thinking" or am I headed
down the "path of ruin"?

Tks in advance,
Jim May

Before Print - Case Select?
 

Except for someone changing the sheet name, I don't see any problems.

--
Regards,
Tom Ogilvy


"Jim May" wrote in message
...
Thanks Tom for the input; I failed to mention that each sheet also has
its own
parculiar Header info (and differing Column stretch) so I ended up doing
as
follows:

It seems to work - there are 3 other sheets in the Wb which are static and
I
rarely
print, so they are not "registered" in the Before Print Handler. Do you
see
any "looming-problems"?
TIA,
Jim May

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ShtName = ActiveSheet.Name
Select Case ShtName
Case Is = "BinderFinal"
Call PrtBinderFinal
Case Is = "Co41ClinicSort"
Call PrtCo41ClinicSort
Case Else
Exit Sub
End Select
End Sub

Sub PrtBinderFinal()
Lrow = Cells(Rows.Count, "B").End(xlUp).Row
With ActiveSheet
.PageSetup.PrintArea = "A5:K" & Lrow
.PageSetup.CenterHeader = "Cash Account " _
& "General Ledger Report " _
& "for Month of " & Format(.Range("C5"), "mmmm - yyyy")
End With
End Sub
Sub PrtCo41ClinicSort()
Lrow = Cells(Rows.Count, "I").End(xlUp).Row
With ActiveSheet
.PageSetup.PrintArea = "A5:O" & Lrow
.PageSetup.CenterHeader = "CMG Cash Accounts " _
& "General Ledger Report " _
& "for Month of " & Format(.Range("C5"), "mmmm - yyyy")
End With
End Sub



"Tom Ogilvy" wrote:

If all your doing is setting the printarea, then I don't see any reason
to
have any modules or additional code outside the beforeprint event.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet, Lastrow As Long
For Each sh In ActiveWindow.SelectedSheets
Lastrow = sh.Cells(Rows.Count, "A").End(xlUp)
sh.PageSetup.PrintArea = "'" & sh.Name & _
"'!A5:P" & Lastrow
Next

End Sub

If you have some peculiar printing situation then just process the whole
workbook each time

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet, Lastrow As Long
For Each sh In ThisWorkbook.Worksheets
Lastrow = sh.Cells(Rows.Count, "A").End(xlUp)
sh.PageSetup.PrintArea = "'" & sh.Name & _
"'!A5:P" & Lastrow
Next

End Sub

If you only want to do the 3 sheets, then



Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet, Lastrow As Long
For Each sh In ThisWorkbook.Worksheets
If instr(1,sh.Name,"mynum",vbTextCompare) then
Lastrow = sh.Cells(Rows.Count, "A").End(xlUp)
sh.PageSetup.PrintArea = "'" & sh.Name & _
"'!A5:P" & Lastrow
End if
Next

End Sub

--
Regards,
Tom Ogilvy


"Jim May" wrote:

In a workbook I have 3 sheets where the number of rows (I wish to
print)
varys each month. SO that I wouldn't have to manually set the print
range I
thought I'd set up a macro whcih would 1) find the last active row and
set
the printarea to:
Pagesetup.PrintArea = "A5:P" & Lrow
My three sheets are MyNum1, MyNum2, Mynum3 say.
I thought I might use the Before Print WB event to run things, but
obviously
can have only one Before Print Event, so I thought maybe a Select case
Statement might
work where I have 3 different standard modules, one for each case
My Before Print handler would include the Case Select
With choices PMyNum1, PMyNum2, PMuNum3

The 3 standard modules would be named PMyNum1, PMyNum2, PMuNum3
with the details. Am I proceeding correctly "in my thinking" or am I
headed
down the "path of ruin"?

Tks in advance,
Jim May

Before Print - Case Select?
 

Thanks for looking over;
Jim May

"Tom Ogilvy" wrote:

Except for someone changing the sheet name, I don't see any problems.

--
Regards,
Tom Ogilvy


"Jim May" wrote in message
...
Thanks Tom for the input; I failed to mention that each sheet also has
its own
parculiar Header info (and differing Column stretch) so I ended up doing
as
follows:

It seems to work - there are 3 other sheets in the Wb which are static and
I
rarely
print, so they are not "registered" in the Before Print Handler. Do you
see
any "looming-problems"?
TIA,
Jim May

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ShtName = ActiveSheet.Name
Select Case ShtName
Case Is = "BinderFinal"
Call PrtBinderFinal
Case Is = "Co41ClinicSort"
Call PrtCo41ClinicSort
Case Else
Exit Sub
End Select
End Sub

Sub PrtBinderFinal()
Lrow = Cells(Rows.Count, "B").End(xlUp).Row
With ActiveSheet
.PageSetup.PrintArea = "A5:K" & Lrow
.PageSetup.CenterHeader = "Cash Account " _
& "General Ledger Report " _
& "for Month of " & Format(.Range("C5"), "mmmm - yyyy")
End With
End Sub
Sub PrtCo41ClinicSort()
Lrow = Cells(Rows.Count, "I").End(xlUp).Row
With ActiveSheet
.PageSetup.PrintArea = "A5:O" & Lrow
.PageSetup.CenterHeader = "CMG Cash Accounts " _
& "General Ledger Report " _
& "for Month of " & Format(.Range("C5"), "mmmm - yyyy")
End With
End Sub



"Tom Ogilvy" wrote:

If all your doing is setting the printarea, then I don't see any reason
to
have any modules or additional code outside the beforeprint event.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet, Lastrow As Long
For Each sh In ActiveWindow.SelectedSheets
Lastrow = sh.Cells(Rows.Count, "A").End(xlUp)
sh.PageSetup.PrintArea = "'" & sh.Name & _
"'!A5:P" & Lastrow
Next

End Sub

If you have some peculiar printing situation then just process the whole
workbook each time

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet, Lastrow As Long
For Each sh In ThisWorkbook.Worksheets
Lastrow = sh.Cells(Rows.Count, "A").End(xlUp)
sh.PageSetup.PrintArea = "'" & sh.Name & _
"'!A5:P" & Lastrow
Next

End Sub

If you only want to do the 3 sheets, then



Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet, Lastrow As Long
For Each sh In ThisWorkbook.Worksheets
If instr(1,sh.Name,"mynum",vbTextCompare) then
Lastrow = sh.Cells(Rows.Count, "A").End(xlUp)
sh.PageSetup.PrintArea = "'" & sh.Name & _
"'!A5:P" & Lastrow
End if
Next

End Sub

--
Regards,
Tom Ogilvy


"Jim May" wrote:

In a workbook I have 3 sheets where the number of rows (I wish to
print)
varys each month. SO that I wouldn't have to manually set the print
range I
thought I'd set up a macro whcih would 1) find the last active row and
set
the printarea to:
Pagesetup.PrintArea = "A5:P" & Lrow
My three sheets are MyNum1, MyNum2, Mynum3 say.
I thought I might use the Before Print WB event to run things, but
obviously
can have only one Before Print Event, so I thought maybe a Select case
Statement might
work where I have 3 different standard modules, one for each case
My Before Print handler would include the Case Select
With choices PMyNum1, PMyNum2, PMuNum3

The 3 standard modules would be named PMyNum1, PMyNum2, PMuNum3
with the details. Am I proceeding correctly "in my thinking" or am I
headed
down the "path of ruin"?

Tks in advance,
Jim May