Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Page breaks
I have a spreadsheet that has client numbers in the first column. I'd like to
be able to automatically put in page breaks for each client number. For example, say rows 1 through 5 are client number 1, rows 6 & 7 are client number 2, 8 through 15 are client 3 and so on. I'd like to have auto page breaks at row 6, 8, 16, etc. Any ideas? |
#2
|
|||
|
|||
Rob, try this
Sub InsertBreaks() Set rng = Range(Cells(2, 1), _ Cells(Rows.Count, 1).End(xlUp)) For Each cell In rng If Trim(cell.Value) < _ Trim(cell.Offset(-1, 0).Value) Then ActiveSheet.HPageBreaks.Add cell End If Next End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Rob" wrote in message ... I have a spreadsheet that has client numbers in the first column. I'd like to be able to automatically put in page breaks for each client number. For example, say rows 1 through 5 are client number 1, rows 6 & 7 are client number 2, 8 through 15 are client 3 and so on. I'd like to have auto page breaks at row 6, 8, 16, etc. Any ideas? |
#3
|
|||
|
|||
Rob
Sub Insert_Pbreak() Dim OldVal As String Dim Rng As Range With Application .Calculation = xlManual .ScreenUpdating = False End With OldVal = Range("A1") StartTime = Timer For Each Rng In Range("A1:A300") '<< change range If Rng.text < OldVal Then Rng.PageBreak = xlPageBreakManual OldVal = Rng.text End If Next Rng MsgBox Timer - StartTime With Application .Calculation = xlAutomatic .ScreenUpdating = True End With Note: setting pagebreaks for a great whack of clients will take a while. 200 clients took 65 seconds on my 2.6Ghz Pentium 4 Gord Dibben Excel MVP On Fri, 28 Jan 2005 08:53:03 -0800, Rob wrote: I have a spreadsheet that has client numbers in the first column. I'd like to be able to automatically put in page breaks for each client number. For example, say rows 1 through 5 are client number 1, rows 6 & 7 are client number 2, 8 through 15 are client 3 and so on. I'd like to have auto page breaks at row 6, 8, 16, etc. Any ideas? |
#4
|
|||
|
|||
Paul
I like this. So very much much faster than the clunk I posted. Thanks, Gord On Fri, 28 Jan 2005 12:43:58 -0500, "Paul B" wrote: Rob, try this Sub InsertBreaks() Set rng = Range(Cells(2, 1), _ Cells(Rows.Count, 1).End(xlUp)) For Each cell In rng If Trim(cell.Value) < _ Trim(cell.Offset(-1, 0).Value) Then ActiveSheet.HPageBreaks.Add cell End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Page Breaks | Excel Worksheet Functions | |||
How to add page breaks to embedded spreadsheet | Excel Worksheet Functions | |||
Forcing page breaks | Excel Discussion (Misc queries) | |||
Page breaks don't show on screen and don't print separately. It i. | Excel Discussion (Misc queries) | |||
How do I stop automatic page breaks in excel | Excel Worksheet Functions |