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

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

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

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

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

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