Print Pages by Page Number

[RyanH]

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!

 

[OssieMac]

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]

Thanks for the Reply Ossie. Unfortunately, I don't think this is what I am
looking for. Basically I have code that sorts Sheets("Global Production
Schedule") by each individual sales person which is located in Column "B".
Then it inserts a horizontal page break to separate the entire schedule by
sales person. I then have a userfrom with a checkbox assigned to each sales
person. I want the user to check which sales person schedules she wants to
print then click the print button on my userform. I need the code to then
find which page number(s) is associated with the sales person(s) she checked
and print them. Below is my code I have so far that sorts the schedule,
separates the schedules, and set the print area:

Sub SalesSchedule()

Application.ScreenUpdating = False

Sheets("Global Production Schedule").Rows("3:400").Sort _
Key1:=Worksheets("Global Production Schedule").Range("B1"), _
Key2:=Worksheets("Global Production Schedule").Range("K1"), _
Key3:=Worksheets("Global Production Schedule").Range("J1")
Sheets("Global Production Schedule").Select

Call SetPrintArea
Call SetVPageBreak

Dim rng As Range
Dim i As Integer

For Each rng In ActiveSheet.Range("B3:B400")
If Not rng.Row = 3 Then
If (Not rng.Value = rng.Offset(-1).Value) Then
ActiveSheet.HPageBreaks.Add Before:=Range("A" & rng.Row)
End If
End If
Next rng

Application.ScreenUpdating = True

EndMsg = MsgBox("The Sales Schedule has been produced, would you like to
Print?", vbYesNo)
If EndMsg <> vbYes Then Exit Sub

PrintUserForm.Show

***** THIS IS WERE I WANT CODE TO PRINT SCECIFIC SCHEDULES. *****

End Sub

Thanks Again!!

 

[OssieMac]

Hi Ryan,

I can only offer pointers as to how you might achieve the desired result
because there is still too much that I can't really picture.

I would have thought that you could still incorporate the principles of
setting the print area for each sales person by finding the first instance of
the sales person in column B and then find the change to another sales person
as you have done for the HPageBreaks.

Also how do you know how many pages for each sales person? Is there always
only one or can there be multiple pages. If multiple pages, is it always the
same number of pages for each sales person? I would think it possible that
there could be auto page breaks between the hard page breaks. Multiple pages
for a sales person would handle itself if you set the print areas
individually prior to printing.

If you still want to run with the page number to print then I would suggest
that you set up a table somewhere in your workbook and when inserting the
page breaks you keep a record of the page numbers pertaining to each sales
person. You can then use a method of looking up the page numbers associated
with the sales person. However, if there are multiple pages for each sales
person, then where you insert hard page breaks, you also need to count rows
and insert intermediate hard breaks as well which will give you the
opportunity to count all pages. The table would then need 2 columns for the
page numbers (first and last page).

Anyway I hope that I have provided some insight that will help you to come
up with something which will meet your needs.

Regards,

OssieMac

 

[RyanH]

Thanks for sticking with this issue Ossie! Each sales persons schedule can
be 1 to 3 pages long, maybe longer in the future. Is there a Keyword or any
syntax that can find the page numbers of the selected sales people and then
tell the printer to only print those selected pages?

 

[david s.]

thank you "OssieMac"

just wanted to say thanks for your input here... this solved a problem i
have been agonizing over for days. this is how your code ended up on my form:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

If ActiveSheet.Range("B125") = 0 And ActiveSheet.Range("B166") = 0 Then

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

ElseIf ActiveSheet.Range("B125") = 0 And ActiveSheet.Range("B166") <> 0 Then

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

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

ElseIf ActiveSheet.Range("B125") <> 0 And ActiveSheet.Range("B166") = 0 Then

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

Else: ActiveWindow.SelectedSheets. _
PrintOut From:="1", To:="5", Copies:=1, Collate:=True

End If

End Sub

worked like a charm; i had been trying to set a print area that was not
continuous, and Union() would not do it. your solution in this post did the
trick.