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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > > >
>
>
>