Page Break Help please

[allison]

I am struggling with getting a page break in the correct location.

I wrote code to insert the header text above each team members information - the idea is that the header would be the first line on each sheet. So, right now, my data looks like:

Supervisor
Member1
Supervisor
Member2
Member2
Member2
Supervisor
Member3

I want the page break to be on the last line of the member name. However, the code I've written can get it under the Supervisor line, or I've been able to get it so that it's above the last Member line.

Could someone help me correct the error of my ways, please? Here is the code that I've got:

Sub InsertPBs()
Dim rngMyRange As Range, rngCell As Range

With Worksheets("Active")
Set rngMyRange = .Range(.Range("A1"), .Range("A900").End(xlUp))
'the range to work with
For Each rngCell In rngMyRange
'loop through the range
If rngCell.Value = "Supervisor" Then
.HPageBreaks.Add Before:=rngCell.Offset(1, 0)
End If
Next
End With

End Sub

[rory]

Unless I misunderstood, this line:
[VBA].HPageBreaks.Add Before:=rngCell.Offset(1, 0)[/VBA]
should be:
[VBA].HPageBreaks.Add Before:=rngCell[/VBA]

[allison]

I probably should have mentioned that I tried that. I get a run time error1004 application defined or object defined error on the line when I tried that.

.HPageBreaks.Add Before:=rngCell

Makes no sense to me.

[allison]

Oops. I figured out what was causing the run time error and fixed that. Works well. Thanks.

However, it's adding in more breaks than where I want them (and more frequently than a normal one due to page length). Any thoughts as to why that might be happening?

[rory]

Ah, your data starts in row 1 - try this:
[VBA]
Sub InsertPBs()
Dim rngMyRange As Range, rngCell As Range

With Worksheets("Active")
Set rngMyRange = .Range(.Range("A1"), .Range("A900").End(xlUp))
'the range to work with
For Each rngCell In rngMyRange
'loop through the range
If rngCell.Value = "Supervisor" And rngCell.Row > 1 Then _
.HPageBreaks.Add Before:=rngCell
Next rngCell
End With
End Sub

[/VBA]