software

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=(quot;student1quot;,quot;student2quot;,quot;student3quot;)
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.

--
David

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

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

David wrote:
gt;
gt; Using XL2000, I have a workbook that uses manual page breaks for a large
gt; group of students. Each student name is followed by a list of classes they
gt; attended in the month. Each page has that student's name followed by their
gt; class attendance. Each month I print out that file, but I would like to
gt; limit the printed pages to a preset list of students by name. With the
gt; addition/deletion of students from the class, the student's page number may
gt; vary.
gt;
gt; I'm thinking there's a macro way to cycle through a set array of names and
gt; print out only those pages.
gt;
gt; Something like:
gt; StudentArr=(quot;student1quot;,quot;student2quot;,quot;student3quot;)
gt; L = StudentArr
gt; For i = Lbound(L) To Ubound(L)
gt; Application.Find (i)
gt; Application.PrintOut what:= that page
gt; Next i
gt;
gt; As a second project, I would like to print pages for all students NOT in
gt; that list.
gt;
gt; Any help would be appreciated.
gt;
gt; --
gt; David

--

Dave Peterson

Dave Peterson wrote

gt; Ron de Bruin has some code that will generate new sheets for each
gt; horizontal pagebreak.
gt; www.rondebruin.nl/hpagebreaks.htm
gt;
gt; Maybe you could modify it to just print the page you want.
gt;
gt; David wrote:
gt;gt;
gt;gt; Using XL2000, I have a workbook that uses manual page breaks for a
gt;gt; large group of students. Each student name is followed by a list of
gt;gt; classes they attended in the month. Each page has that student's name
gt;gt; followed by their class attendance. Each month I print out that file,
gt;gt; but I would like to limit the printed pages to a preset list of
gt;gt; students by name. With the addition/deletion of students from the
gt;gt; class, the student's page number may vary.
gt;gt;
gt;gt; I'm thinking there's a macro way to cycle through a set array of
gt;gt; names and print out only those pages.
gt;gt;
gt;gt; Something like:
gt;gt; StudentArr=(quot;student1quot;,quot;student2quot;,quot;student3quot;)
gt;gt; L = StudentArr
gt;gt; For i = Lbound(L) To Ubound(L)
gt;gt; Application.Find (i)
gt;gt; Application.PrintOut what:= that page
gt;gt; Next i
gt;gt;
gt;gt; As a second project, I would like to print pages for all students NOT
gt;gt; in that list.
gt;gt;
gt;gt; Any help would be appreciated.
gt;gt;
gt;gt; --
gt;gt; David
gt;

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 wrotegt; Something like:
gt; StudentArr=(quot;student1quot;,quot;student2quot;,quot;student3quot;)
gt; L = StudentArr
gt; For i = Lbound(L) To Ubound(L)
gt; Application.Find (i)
gt; Application.PrintOut what:= that page
gt; Next iHere'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(quot;A:Aquot;).Find(What:=quot;turner, davidquot;).Activate
HPC = ActiveSheet.HPageBreaks.Count 1
VPC = 1
NumPage = 1
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row gt; ActiveCell.Row Then Exit For
NumPage = NumPage VPC
Next HPB
Sheets(1).PrintOut From:=NumPage, To:=NumPage, Preview:=True 'lt;would change
to actually print
End Sub

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

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(quot;turner, davidquot;, quot;turner, kathleenquot;)

For iCtr = LBound(myNames) To UBound(myNames)
Set FoundCell = Range(quot;A:Aquot;).Find(What:=myNames(iCtr))
If FoundCell Is Nothing Then
MsgBox myNames(iCtr) amp; quot; wasn't foundquot;
Else
HPC = ActiveSheet.HPageBreaks.Count 1
VPC = 1
NumPage = 1
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row gt; 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 quot;as integerquot; to quot;as longquot;.)

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 wrote:
gt;
gt; David wrote
gt;
gt; gt; Something like:
gt; gt; StudentArr=(quot;student1quot;,quot;student2quot;,quot;student3quot;)
gt; gt; L = StudentArr
gt; gt; For i = Lbound(L) To Ubound(L)
gt; gt; Application.Find (i)
gt; gt; Application.PrintOut what:= that page
gt; gt; Next i
gt;
gt; Here's some code I found and adapted for testing for a single name:
gt;
gt; Sub test()
gt; Dim VPC As Integer, HPC As Integer
gt; Dim HPB As HPageBreak
gt; Dim NumPage As Integer
gt; Range(quot;A:Aquot;).Find(What:=quot;turner, davidquot;).Activate
gt; HPC = ActiveSheet.HPageBreaks.Count 1
gt; VPC = 1
gt; NumPage = 1
gt; For Each HPB In ActiveSheet.HPageBreaks
gt; If HPB.Location.Row gt; ActiveCell.Row Then Exit For
gt; NumPage = NumPage VPC
gt; Next HPB
gt; Sheets(1).PrintOut From:=NumPage, To:=NumPage, Preview:=True 'lt;would change
gt; to actually print
gt; End Sub
gt;
gt; Can someone show me how to process an array of names?
gt; --
gt; David

--

Dave Peterson

Dave Peterson wrote

gt; Untested, but it uses the code you posted:
gt;
gt; Option Explicit
gt; Sub test()
gt; Dim VPC As Long
gt; Dim HPC As Long
gt; Dim HPB As HPageBreak
gt; Dim NumPage As Long
gt; Dim myNames As Variant
gt; Dim iCtr As Long
gt; Dim FoundCell As Range
gt;
gt; myNames = Array(quot;turner, davidquot;, quot;turner, kathleenquot;)
gt;
gt; For iCtr = LBound(myNames) To UBound(myNames)
gt; Set FoundCell = Range(quot;A:Aquot;).Find(What:=myNames(iCtr))
gt; If FoundCell Is Nothing Then
gt; MsgBox myNames(iCtr) amp; quot; wasn't foundquot;
gt; Else
gt; HPC = ActiveSheet.HPageBreaks.Count 1
gt; VPC = 1
gt; NumPage = 1
gt; For Each HPB In ActiveSheet.HPageBreaks
gt; If HPB.Location.Row gt; ActiveCell.Row Then Exit For
gt; NumPage = NumPage VPC
gt; Next HPB
gt; Sheets(1).PrintOut From:=NumPage, To:=NumPage,
gt; Preview:=True
gt; End If
gt; Next iCtr
gt; End Sub
gt;
gt;
gt; (I changed quot;as integerquot; to quot;as longquot;.)
gt;
gt; You may want to provide all the parms to your .find statement.
gt; Excel/VBA remembers what was used. So you may be disappointed if the
gt; 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(quot;A:Aquot;).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(quot;turner, davidquot;, quot;turner, kathleenquot;)
For iCtr = LBound(MyNames) To UBound(MyNames)
Range(quot;A:Aquot;).Find(What:=(MyNames(iCtr))).Activate
HPC = ActiveSheet.HPageBreaks.Count 1
VPC = 1
NumPage = 1
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row gt; 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.

--
David

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

If HPB.Location.Row gt; ActiveCell.Row Then Exit For
to
If HPB.Location.Row gt; foundcell.Row Then Exit For

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

David wrote:
gt;
gt; Dave Peterson wrote
gt;
gt; gt; Untested, but it uses the code you posted:
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub test()
gt; gt; Dim VPC As Long
gt; gt; Dim HPC As Long
gt; gt; Dim HPB As HPageBreak
gt; gt; Dim NumPage As Long
gt; gt; Dim myNames As Variant
gt; gt; Dim iCtr As Long
gt; gt; Dim FoundCell As Range
gt; gt;
gt; gt; myNames = Array(quot;turner, davidquot;, quot;turner, kathleenquot;)
gt; gt;
gt; gt; For iCtr = LBound(myNames) To UBound(myNames)
gt; gt; Set FoundCell = Range(quot;A:Aquot;).Find(What:=myNames(iCtr))
gt; gt; If FoundCell Is Nothing Then
gt; gt; MsgBox myNames(iCtr) amp; quot; wasn't foundquot;
gt; gt; Else
gt; gt; HPC = ActiveSheet.HPageBreaks.Count 1
gt; gt; VPC = 1
gt; gt; NumPage = 1
gt; gt; For Each HPB In ActiveSheet.HPageBreaks
gt; gt; If HPB.Location.Row gt; ActiveCell.Row Then Exit For
gt; gt; NumPage = NumPage VPC
gt; gt; Next HPB
gt; gt; Sheets(1).PrintOut From:=NumPage, To:=NumPage,
gt; gt; Preview:=True
gt; gt; End If
gt; gt; Next iCtr
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt; (I changed quot;as integerquot; to quot;as longquot;.)
gt; gt;
gt; gt; You may want to provide all the parms to your .find statement.
gt; gt; Excel/VBA remembers what was used. So you may be disappointed if the
gt; gt; user (or code) did a .find with matchcase:=true (for example).
gt;
gt; Since code after Else calculates for active cell, I simply had to add
gt; FoundCell.Activate after Else
gt;
gt; or if I were positive all names in the array were present, I could
gt; eliminate any FoundCell code altogether with
gt;
gt; Range(quot;A:Aquot;).Find(What:=(myNames(iCtr))).Activate
gt;
gt; My tested, working code:
gt; Sub PrintMine()
gt; Dim VPC As Long, HPC As Long, HPB As HPageBreak
gt; Dim iCtr As Long, NumPage As Long, MyNames As Variant
gt; MyNames = Array(quot;turner, davidquot;, quot;turner, kathleenquot;)
gt; For iCtr = LBound(MyNames) To UBound(MyNames)
gt; Range(quot;A:Aquot;).Find(What:=(MyNames(iCtr))).Activate
gt; HPC = ActiveSheet.HPageBreaks.Count 1
gt; VPC = 1
gt; NumPage = 1
gt; For Each HPB In ActiveSheet.HPageBreaks
gt; If HPB.Location.Row gt; ActiveCell.Row Then Exit For
gt; NumPage = NumPage VPC
gt; Next HPB
gt; Sheets(1).PrintOut From:=NumPage, To:=NumPage, Preview:=True
gt; Next iCtr
gt; End Sub
gt;
gt; Thanks for providing needed array handling syntax.
gt;
gt; --
gt; David

--

Dave Peterson

Dave Peterson wrote

gt; If I had noticed that activecell reference, I would have changed this
gt; line:
gt;
gt; If HPB.Location.Row gt; ActiveCell.Row Then Exit For
gt; to
gt; If HPB.Location.Row gt; foundcell.Row Then Exit For
gt;
gt; Personally, I make enough typing mistakes that I'd want that check
gt; included.
gt;

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 quot;borrowedquot; and trimmed
that had a check for vertical page breaks which weren't necessary to meet
my needs.

--
David