The code below automatically sets the print area (1 to 7 pages) based on 1
cell in each page being populated. You must start at the last page and work
back (the first page is always populated). Page 1 is not always required, the
first if statement determines whether to print page 1 or not. A separate
macro hides page 1 if not required.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Set Sheets to Print
Application.ScreenUpdating = False
If Range("InstrmntTP7") < "" Then
If Range("StndrdTP1") < "" Then
ActiveSheet.PageSetup.PrintArea = "Pg1:Pg7"
Else
ActiveSheet.PageSetup.PrintArea = "Pg2:Pg7"
End If
ElseIf Range("InstrmntTP6") < "" Then
If Range("StndrdTP1") < "" Then
ActiveSheet.PageSetup.PrintArea = "Pg1:Pg6"
Else
ActiveSheet.PageSetup.PrintArea = "Pg2:Pg6"
End If
ElseIf Range("InstrmntTP5") < "" Then
If Range("StndrdTP1") < "" Then
ActiveSheet.PageSetup.PrintArea = "Pg1:Pg5"
Else
ActiveSheet.PageSetup.PrintArea = "Pg2:Pg5"
End If
ElseIf Range("InstrmntTP4") < "" Then
If Range("StndrdTP1") < "" Then
ActiveSheet.PageSetup.PrintArea = "Pg1:Pg4"
Else
ActiveSheet.PageSetup.PrintArea = "Pg2:Pg4"
End If
ElseIf Range("InstrmntTP3") < "" Then
If Range("StndrdTP1") < "" Then
ActiveSheet.PageSetup.PrintArea = "Pg1:Pg3"
Else
ActiveSheet.PageSetup.PrintArea = "Pg2:Pg3"
End If
ElseIf Range("InstrmntTP2") < "" Then
If Range("StndrdTP1") < "" Then
ActiveSheet.PageSetup.PrintArea = "Pg1:Pg2"
Else
ActiveSheet.PageSetup.PrintArea = "Pg2"
End If
ElseIf Range("StndrdTP1") < "" Then
ActiveSheet.PageSetup.PrintArea = "Pg1"
Else: Cancel = True
End If
Application.ScreenUpdating = True
End Sub

I think you can easily adapt this to meet your needs.
Lou


"Gord Dibben" wrote:

You can use ViewCustom Views to set up two different print ranges for your
users to pick from.

Or you could use a macro to print a selected range

You can use the name of the custom view as a parameter in your macro or just
direcly name the range to print.

I prefer the custom views method because you can hide rows and columns within
one print range.


Gord Dibben MS Excel MVP

On Thu, 4 Jan 2007 11:36:01 -0800, zip82dah
wrote:

I am working with new Excel users. I have a worksheet that sometimes needs
one print range and sometimes another. How can I predefine those ranges so
the users can just select PrintArea1 or PrintArea2?