Hi Ryan,

I am assuming that you are only looking for pointers on how to go about this
so I'll post some code that I am sure will help. The code actually resets the
print areas but you indicated that you wanted to be able to select by the
page number so I have included some code at the bottom that should point you
in the right direction to modify the other code because the principles are
the same.

When you open the VBA editor, double click ThisWorkbook and paste it in there.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsName As String
Dim myPage As Variant

wsName = ActiveSheet.Name

'Case routine ensures that macro only runs with the
'required worksheets and exits sub with other worksheets
Select Case wsName
Case "MySchedule"
Cancel = True 'Cancel initial print call
GoTo printMySchedule

Case "YourReport"
'Can have separate routines for various worksheets
'Cancel = True 'Cancel initial print call
'GoTo printYourSchedule

Case Else
'Handles worksheets that do not require
'special routines so exit and print as normal
Exit Sub

End Select

printMySchedule:

'Following line inserted for testing purposes
myPage = "person1"

'Can replace following code with a loop to cover multiple selections
Select Case myPage
Case "person1"
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$59"
Case "Person2"
ActiveSheet.PageSetup.PrintArea = "$J$1:$R$59"
Case Else
MsgBox "No valid sales person selected for printing"
Exit Sub
End Select

'Suppress recursive calls to this sub
Application.EnableEvents = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.EnableEvents = True

End Sub

Code to use if selecting by page number to print.
Dim myPageNbr As Single
myPageNbr = 2

ActiveWindow.SelectedSheets.PrintOut _
From:=myPageNbr, To:=myPageNbr, Copies:=1, Collate:=True


Feel free to get back to me if you still have any problems. (It will help if
you post the code as far as you have got)

Regards,

OssieMac



"RyanH" wrote:

I think this is an easy one for an experienced VBA guy unlike myself (still
learning). I have a Worksheet that is Sorted by the sales persons initials
in Column B. I then have horizontal pages breaks inserted so I can seperate
each sales persons individual schedule for the day. I designed a UserForm
that has a checkbox by each sales persons name. I want the user to be able
to check the sales schedules they want to print, hit print, and only the
checked schedules would be printed.

Thanks in advance!