Print a preset group of pages

[David]

Using XL2000, I have a workbook that uses manual page breaks for a large
group of students. Each student name is followed by a list of classes they
attended in the month. Each page has that student's name followed by their
class attendance. Each month I print out that file, but I would like to
limit the printed pages to a preset list of students by name. With the
addition/deletion of students from the class, the student's page number may
vary.

I'm thinking there's a macro way to cycle through a set array of names and
print out only those pages.

Something like:
StudentArr=("student1","student2","student3")
L = StudentArr
For i = Lbound(L) To Ubound(L)
Application.Find (i)
Application.PrintOut what:= that page
Next i

As a second project, I would like to print pages for all students NOT in
that list.

Any help would be appreciated.

 

[Dave Peterson]

Ron de Bruin has some code that will generate new sheets for each horizontal
pagebreak.
http://www.rondebruin.nl/hpagebreaks.htm

Maybe you could modify it to just print the page you want.

 

[David]

Dave Peterson wrote

Ron de Bruin has some code that will generate new sheets for each
horizontal pagebreak.
http://www.rondebruin.nl/hpagebreaks.htm

Maybe you could modify it to just print the page you want.

Had a quick look. Would take considerable altering (I think) to get new
sheets to retain format of original. I'll play, but still would like to
see if someone could do it like I suggested.

 

[David]

David wrote

Something like:
StudentArr=("student1","student2","student3")
L = StudentArr
For i = Lbound(L) To Ubound(L)
Application.Find (i)
Application.PrintOut what:= that page
Next i

Here's some code I found and adapted for testing for a single name:

Sub test()
Dim VPC As Integer, HPC As Integer
Dim HPB As HPageBreak
Dim NumPage As Integer
Range("A:A").Find(What:="turner, david").Activate
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
NumPage = 1
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row > ActiveCell.Row Then Exit For
NumPage = NumPage + VPC
Next HPB
Sheets(1).PrintOut From:=NumPage, To:=NumPage, Preview:=True '<would change
to actually print
End Sub

Can someone show me how to process an array of names?

 

[Dave Peterson]

Untested, but it uses the code you posted:

Option Explicit
Sub test()
Dim VPC As Long
Dim HPC As Long
Dim HPB As HPageBreak
Dim NumPage As Long
Dim myNames As Variant
Dim iCtr As Long
Dim FoundCell As Range

myNames = Array("turner, david", "turner, kathleen")

For iCtr = LBound(myNames) To UBound(myNames)
Set FoundCell = Range("A:A").Find(What:=myNames(iCtr))
If FoundCell Is Nothing Then
MsgBox myNames(iCtr) & " wasn't found"
Else
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
NumPage = 1
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row > ActiveCell.Row Then Exit For
NumPage = NumPage + VPC
Next HPB
Sheets(1).PrintOut From:=NumPage, To:=NumPage, Preview:=True
End If
Next iCtr
End Sub


(I changed "as integer" to "as long".)

You may want to provide all the parms to your .find statement. Excel/VBA
remembers what was used. So you may be disappointed if the user (or code) did a
..find with matchcase:=true (for example).

 

[David]

Dave Peterson wrote

Untested, but it uses the code you posted:

Option Explicit
Sub test()
Dim VPC As Long
Dim HPC As Long
Dim HPB As HPageBreak
Dim NumPage As Long
Dim myNames As Variant
Dim iCtr As Long
Dim FoundCell As Range

myNames = Array("turner, david", "turner, kathleen")

For iCtr = LBound(myNames) To UBound(myNames)
Set FoundCell = Range("A:A").Find(What:=myNames(iCtr))
If FoundCell Is Nothing Then
MsgBox myNames(iCtr) & " wasn't found"
Else
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
NumPage = 1
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row > ActiveCell.Row Then Exit For
NumPage = NumPage + VPC
Next HPB
Sheets(1).PrintOut From:=NumPage, To:=NumPage,
Preview:=True
End If
Next iCtr
End Sub


(I changed "as integer" to "as long".)

You may want to provide all the parms to your .find statement.
Excel/VBA remembers what was used. So you may be disappointed if the
user (or code) did a .find with matchcase:=true (for example).

Since code after Else calculates for active cell, I simply had to add
FoundCell.Activate after Else

or if I were positive all names in the array were present, I could
eliminate any FoundCell code altogether with

Range("A:A").Find(What:=(myNames(iCtr))).Activate

My tested, working code:
Sub PrintMine()
Dim VPC As Long, HPC As Long, HPB As HPageBreak
Dim iCtr As Long, NumPage As Long, MyNames As Variant
MyNames = Array("turner, david", "turner, kathleen")
For iCtr = LBound(MyNames) To UBound(MyNames)
Range("A:A").Find(What:=(MyNames(iCtr))).Activate
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
NumPage = 1
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row > ActiveCell.Row Then Exit For
NumPage = NumPage + VPC
Next HPB
Sheets(1).PrintOut From:=NumPage, To:=NumPage, Preview:=True
Next iCtr
End Sub

Thanks for providing needed array handling syntax.

 

[Dave Peterson]

If I had noticed that activecell reference, I would have changed this line:

If HPB.Location.Row > ActiveCell.Row Then Exit For
to
If HPB.Location.Row > foundcell.Row Then Exit For

Personally, I make enough typing mistakes that I'd want that check included.

 

[David]

Dave Peterson wrote

If I had noticed that activecell reference, I would have changed this
line:

If HPB.Location.Row > ActiveCell.Row Then Exit For
to
If HPB.Location.Row > foundcell.Row Then Exit For

Personally, I make enough typing mistakes that I'd want that check
included.

That works, and makes the eye-pleasing difference that focus isn't taken to
activecell during execution. Point taken on including the name check.

Another observation:
VPC isn't even needed. I eliminated VPC = 1 line and changed
NumPage = NumPage + VPC
to
NumPage = NumPage + 1 and nothing broke.

I believe it was included in the original code I "borrowed" and trimmed
that had a check for vertical page breaks which weren't necessary to meet
my needs.