Page breaks

[Guest]

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 B]

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

 

[Gord Dibben]

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

 

[Gord Dibben]

Paul

I like this.

So very much much faster than the clunk I posted.

Thanks, Gord