Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting rows before page break
Hi everyone
I have a list of student names, M/F, D.O.B., Test results etc. sorted by their assigned classroom number. Rooms numbers can vary in sequence and total off i.e. 4,5,6,9,12,16, etc.etc. The number of students in each class can also vary but a classroom will always fit on the one page. I can find the starting row of each room by using a helper column and inserting a blank row and page break. What I would like to do is move the starting row of room 5 down to row 38 (start of page 2 landscape format). Then move room 6 down to row 74 (start of page 3 landscape format) and so on until and including the last room number, to end up with a classroom on each page with spare blank rows for teachers use. The page size ,margins, headers and footers have been set previously. Season Greetings to all. Hope you can help. TIA Bob C |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting rows before page break
Seems it would be easer to just apply an autofilter
(Data=filter=Autofilter) filter on each room, print, filter on the next and so forth. (the dropdown arrows for the filter don't print). -- Regards, Tom Ogilvy Robert Christie wrote in message ... Hi everyone I have a list of student names, M/F, D.O.B., Test results etc. sorted by their assigned classroom number. Rooms numbers can vary in sequence and total off i.e. 4,5,6,9,12,16, etc.etc. The number of students in each class can also vary but a classroom will always fit on the one page. I can find the starting row of each room by using a helper column and inserting a blank row and page break. What I would like to do is move the starting row of room 5 down to row 38 (start of page 2 landscape format). Then move room 6 down to row 74 (start of page 3 landscape format) and so on until and including the last room number, to end up with a classroom on each page with spare blank rows for teachers use. The page size ,margins, headers and footers have been set previously. Season Greetings to all. Hope you can help. TIA Bob C |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting rows before page break
Thank you for reply Tom.
I'm trying to fully automate the formating from a .CSV file to a spreadsheet. A second sheet allows the user to print all classroom sheets or specific classrooms. Using autofilter will not give me the extra blank rows under last name, hence the requirement to move rows down. TIA Bob C -----Original Message----- Seems it would be easer to just apply an autofilter (Data=filter=Autofilter) filter on each room, print, filter on the next and so forth. (the dropdown arrows for the filter don't print). -- Regards, Tom Ogilvy Robert Christie wrote in message ... Hi everyone I have a list of student names, M/F, D.O.B., Test results etc. sorted by their assigned classroom number. Rooms numbers can vary in sequence and total off i.e. 4,5,6,9,12,16, etc.etc. The number of students in each class can also vary but a classroom will always fit on the one page. I can find the starting row of each room by using a helper column and inserting a blank row and page break. What I would like to do is move the starting row of room 5 down to row 38 (start of page 2 landscape format). Then move room 6 down to row 74 (start of page 3 landscape format) and so on until and including the last room number, to end up with a classroom on each page with spare blank rows for teachers use. The page size ,margins, headers and footers have been set previously. Season Greetings to all. Hope you can help. TIA Bob C . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting rows before page break
If you have 100 names and you set the print area to A1:J110, then applying
the filter and printing will include 10 rows at the bottom for each class (barring going beyond the pagebreak). the other way would be to have one fully formatted class sheet, then use the autofilter on your data sheet, copy the rows over to the formatted sheet (only visible rows are copied by default), print (clear that data), and repeat for each class. (or use the advanced filter). Working with pagebreaks is slow and unreliable. For instance, horizontal pagebreaks which don't have data below them, don't even show up in the pagebreaks collection - so you wouldn't be able to find them with code - unless you counted down a fixed number of rows (assumes all row heights are equal). -- Regards, Tom Ogilvy Robert Christie wrote in message ... Thank you for reply Tom. I'm trying to fully automate the formating from a .CSV file to a spreadsheet. A second sheet allows the user to print all classroom sheets or specific classrooms. Using autofilter will not give me the extra blank rows under last name, hence the requirement to move rows down. TIA Bob C -----Original Message----- Seems it would be easer to just apply an autofilter (Data=filter=Autofilter) filter on each room, print, filter on the next and so forth. (the dropdown arrows for the filter don't print). -- Regards, Tom Ogilvy Robert Christie wrote in message ... Hi everyone I have a list of student names, M/F, D.O.B., Test results etc. sorted by their assigned classroom number. Rooms numbers can vary in sequence and total off i.e. 4,5,6,9,12,16, etc.etc. The number of students in each class can also vary but a classroom will always fit on the one page. I can find the starting row of each room by using a helper column and inserting a blank row and page break. What I would like to do is move the starting row of room 5 down to row 38 (start of page 2 landscape format). Then move room 6 down to row 74 (start of page 3 landscape format) and so on until and including the last room number, to end up with a classroom on each page with spare blank rows for teachers use. The page size ,margins, headers and footers have been set previously. Season Greetings to all. Hope you can help. TIA Bob C . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting rows before page break
If you want to pursue you first approach, here is some starter code from I
previous post I made: Here is a method to get an array of horizontal pagebreaks and vertical pagebreaks. The horizontal pagebreaks are a list of rows that have the pagebreak and vertical a list of column numbers: Sub Tester1() Dim horzpbArray() Dim verpbArray() ThisWorkbook.Names.Add Name:="hzPB", _ RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")" ThisWorkbook.Names.Add Name:="vPB", _ RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")" i = 1 While Not IsError(Evaluate("Index(hzPB," & i & ")")) ReDim Preserve horzpbArray(1 To i) horzpbArray(i) = Evaluate("Index(hzPB," & i & ")") i = i + 1 Wend ReDim Preserve horzpbArray(1 To i - 1) Debug.Print "Horizontal Pagebreaks (rows):" For J = LBound(horzpbArray, 1) To UBound(horzpbArray, 1) Debug.Print J, horzpbArray(J) Next J i = 1 While Not IsError(Evaluate("Index(vPB," & i & ")")) ReDim Preserve verpbArray(1 To i) verpbArray(i) = Evaluate("Index(vPB," & i & ")") i = i + 1 Wend ReDim Preserve verpbArray(1 To i - 1) Debug.Print "Vertical Pagebreaks (columns):" For J = LBound(verpbArray, 1) To UBound(verpbArray, 1) Debug.Print J, verpbArray(J) Next J End Sub This uses an Excel 4 macro to get this information. This is much faster than the VBA pagebreak which uses the printer driver and can be very slow. The is a pagebreak property of the range. It can be tested to see if a pagebreak exists if rows(6).pagebreak = xlNone then 'No pagebreak Else ' Has pagebreak if rows(6).pagebreak = xlPageBreakAutomatic then 'Automatic pagebreak elseif rows(6).pagebreak = xlPageBreakManual then ' Manual pagebreak End if End if Combining the above gives: Sub Tester1() Dim horzpbArray() Dim verpbArray() Dim brkType As String ThisWorkbook.Names.Add Name:="hzPB", _ RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")" ThisWorkbook.Names.Add Name:="vPB", _ RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")" i = 1 While Not IsError(Evaluate("Index(hzPB," & i & ")")) ReDim Preserve horzpbArray(1 To i) horzpbArray(i) = Evaluate("Index(hzPB," & i & ")") i = i + 1 Wend ReDim Preserve horzpbArray(1 To i - 1) Debug.Print "Horizontal Pagebreaks (rows):" For j = LBound(horzpbArray, 1) To UBound(horzpbArray, 1) If Rows(horzpbArray(j)).PageBreak = xlNone Then brkType = "None" Else ' Has pagebreak If Rows(horzpbArray(j)).PageBreak = xlPageBreakAutomatic Then brkType = "Automatic" ElseIf Rows(horzpbArray(j)).PageBreak = xlPageBreakManual Then brkType = "Manual" Else brkType = "Unknown" End If End If Debug.Print j, horzpbArray(j), brkType Next j i = 1 While Not IsError(Evaluate("Index(vPB," & i & ")")) ReDim Preserve verpbArray(1 To i) verpbArray(i) = Evaluate("Index(vPB," & i & ")") i = i + 1 Wend ReDim Preserve verpbArray(1 To i - 1) Debug.Print "Vertical Pagebreaks (columns):" For j = LBound(verpbArray, 1) To UBound(verpbArray, 1) If Columns(verpbArray(j)).PageBreak = xlNone Then brkType = "None" Else ' Has pagebreak If Columns(verpbArray(j)).PageBreak = xlPageBreakAutomatic Then brkType = "Automatic" ElseIf Columns(verpbArray(j)).PageBreak = xlPageBreakManual Then brkType = "Manual" Else brkType = "Unknown" End If End If Debug.Print j, verpbArray(j), brkType Next j End Sub Sample Output: Horizontal Pagebreaks (rows): 1 13 Manual 2 24 Manual 3 39 Manual 4 67 Manual 5 87 Manual 6 114 Automatic Vertical Pagebreaks (columns): 1 2 Manual 2 6 Automatic This should get you started. Regards, Tom Ogilvy Robert Christie wrote in message ... Thank you for reply Tom. I'm trying to fully automate the formating from a .CSV file to a spreadsheet. A second sheet allows the user to print all classroom sheets or specific classrooms. Using autofilter will not give me the extra blank rows under last name, hence the requirement to move rows down. TIA Bob C -----Original Message----- Seems it would be easer to just apply an autofilter (Data=filter=Autofilter) filter on each room, print, filter on the next and so forth. (the dropdown arrows for the filter don't print). -- Regards, Tom Ogilvy Robert Christie wrote in message ... Hi everyone I have a list of student names, M/F, D.O.B., Test results etc. sorted by their assigned classroom number. Rooms numbers can vary in sequence and total off i.e. 4,5,6,9,12,16, etc.etc. The number of students in each class can also vary but a classroom will always fit on the one page. I can find the starting row of each room by using a helper column and inserting a blank row and page break. What I would like to do is move the starting row of room 5 down to row 38 (start of page 2 landscape format). Then move room 6 down to row 74 (start of page 3 landscape format) and so on until and including the last room number, to end up with a classroom on each page with spare blank rows for teachers use. The page size ,margins, headers and footers have been set previously. Season Greetings to all. Hope you can help. TIA Bob C . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting rows before page break
Thankyou again Tom
Your second post made me stop and think out side the square, and that will be the way I go for now. Thankyou for the code I'll have a look and a play with it later on, when time permits, it looks intriguing at first glance. And thankyou for your time and effort, very much appreciated. Season greetings to you and your family. Bob C -----Original Message----- If you want to pursue you first approach, here is some starter code from I previous post I made: Here is a method to get an array of horizontal pagebreaks and vertical pagebreaks. The horizontal pagebreaks are a list of rows that have the pagebreak and vertical a list of column numbers: Sub Tester1() Dim horzpbArray() Dim verpbArray() ThisWorkbook.Names.Add Name:="hzPB", _ RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")" ThisWorkbook.Names.Add Name:="vPB", _ RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")" i = 1 While Not IsError(Evaluate("Index(hzPB," & i & ")")) ReDim Preserve horzpbArray(1 To i) horzpbArray(i) = Evaluate("Index(hzPB," & i & ")") i = i + 1 Wend ReDim Preserve horzpbArray(1 To i - 1) Debug.Print "Horizontal Pagebreaks (rows):" For J = LBound(horzpbArray, 1) To UBound(horzpbArray, 1) Debug.Print J, horzpbArray(J) Next J i = 1 While Not IsError(Evaluate("Index(vPB," & i & ")")) ReDim Preserve verpbArray(1 To i) verpbArray(i) = Evaluate("Index(vPB," & i & ")") i = i + 1 Wend ReDim Preserve verpbArray(1 To i - 1) Debug.Print "Vertical Pagebreaks (columns):" For J = LBound(verpbArray, 1) To UBound(verpbArray, 1) Debug.Print J, verpbArray(J) Next J End Sub This uses an Excel 4 macro to get this information. This is much faster than the VBA pagebreak which uses the printer driver and can be very slow. The is a pagebreak property of the range. It can be tested to see if a pagebreak exists if rows(6).pagebreak = xlNone then 'No pagebreak Else ' Has pagebreak if rows(6).pagebreak = xlPageBreakAutomatic then 'Automatic pagebreak elseif rows(6).pagebreak = xlPageBreakManual then ' Manual pagebreak End if End if Combining the above gives: Sub Tester1() Dim horzpbArray() Dim verpbArray() Dim brkType As String ThisWorkbook.Names.Add Name:="hzPB", _ RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")" ThisWorkbook.Names.Add Name:="vPB", _ RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")" i = 1 While Not IsError(Evaluate("Index(hzPB," & i & ")")) ReDim Preserve horzpbArray(1 To i) horzpbArray(i) = Evaluate("Index(hzPB," & i & ")") i = i + 1 Wend ReDim Preserve horzpbArray(1 To i - 1) Debug.Print "Horizontal Pagebreaks (rows):" For j = LBound(horzpbArray, 1) To UBound(horzpbArray, 1) If Rows(horzpbArray(j)).PageBreak = xlNone Then brkType = "None" Else ' Has pagebreak If Rows(horzpbArray(j)).PageBreak = xlPageBreakAutomatic Then brkType = "Automatic" ElseIf Rows(horzpbArray(j)).PageBreak = xlPageBreakManual Then brkType = "Manual" Else brkType = "Unknown" End If End If Debug.Print j, horzpbArray(j), brkType Next j i = 1 While Not IsError(Evaluate("Index(vPB," & i & ")")) ReDim Preserve verpbArray(1 To i) verpbArray(i) = Evaluate("Index(vPB," & i & ")") i = i + 1 Wend ReDim Preserve verpbArray(1 To i - 1) Debug.Print "Vertical Pagebreaks (columns):" For j = LBound(verpbArray, 1) To UBound(verpbArray, 1) If Columns(verpbArray(j)).PageBreak = xlNone Then brkType = "None" Else ' Has pagebreak If Columns(verpbArray(j)).PageBreak = xlPageBreakAutomatic Then brkType = "Automatic" ElseIf Columns(verpbArray(j)).PageBreak = xlPageBreakManual Then brkType = "Manual" Else brkType = "Unknown" End If End If Debug.Print j, verpbArray(j), brkType Next j End Sub Sample Output: Horizontal Pagebreaks (rows): 1 13 Manual 2 24 Manual 3 39 Manual 4 67 Manual 5 87 Manual 6 114 Automatic Vertical Pagebreaks (columns): 1 2 Manual 2 6 Automatic This should get you started. Regards, Tom Ogilvy Robert Christie wrote in message ... Thank you for reply Tom. I'm trying to fully automate the formating from a .CSV file to a spreadsheet. A second sheet allows the user to print all classroom sheets or specific classrooms. Using autofilter will not give me the extra blank rows under last name, hence the requirement to move rows down. TIA Bob C -----Original Message----- Seems it would be easer to just apply an autofilter (Data=filter=Autofilter) filter on each room, print, filter on the next and so forth. (the dropdown arrows for the filter don't print). -- Regards, Tom Ogilvy Robert Christie wrote in message ... Hi everyone I have a list of student names, M/F, D.O.B., Test results etc. sorted by their assigned classroom number. Rooms numbers can vary in sequence and total off i.e. 4,5,6,9,12,16, etc.etc. The number of students in each class can also vary but a classroom will always fit on the one page. I can find the starting row of each room by using a helper column and inserting a blank row and page break. What I would like to do is move the starting row of room 5 down to row 38 (start of page 2 landscape format). Then move room 6 down to row 74 (start of page 3 landscape format) and so on until and including the last room number, to end up with a classroom on each page with spare blank rows for teachers use. The page size ,margins, headers and footers have been set previously. Season Greetings to all. Hope you can help. TIA Bob C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INSERTING ROWS IMMEDIATELY AFTER PAGE BREAK | Excel Discussion (Misc queries) | |||
Inserting page break | Excel Discussion (Misc queries) | |||
Conditional page break if merged rows won't fit on the page. | Excel Worksheet Functions | |||
Inserting header @ page break | Excel Discussion (Misc queries) | |||
Keyboard Shortcut for Inserting a Page Break | Excel Discussion (Misc queries) |