allison
02-08-2008, 04:05 AM
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
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