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