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?

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?

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?

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