Print a preset group of pages
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. -- David |
Print a preset group of pages
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 wrote: 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. -- David -- Dave Peterson |
Print a preset group of pages
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. David wrote: 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. -- David 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 |
Print a preset group of pages
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? -- David |
Print a preset group of pages
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 wrote: 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? -- David -- Dave Peterson |
Print a preset group of pages
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. -- David |
Print a preset group of pages
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 wrote: 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. -- David -- Dave Peterson |
Print a preset group of pages
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. -- David |
All times are GMT +1. The time now is 02:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com