Adding Page Breaks Before Recurring Data in Column "A"

[fallinup00]

Hi,

I am trying to modify the code below to add horiz. page breaks before each instance of the word "Employer" in column A. The code, as it is below, works perfectly, but it places the page break after the row rather than before it.

If I change the line from:
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c.Offset(1) 'adds break after
to:
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c.Offset(0)
or
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c

I receive errors when running the marco.

Any ideas?

Cheers,
-A

Original Code:

Dim c As Range
Dim FirstAddress As String

Set c = Columns("A").Find(What:="Employer", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c.Offset(1)
Set c = Columns("A").FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If

 

[fterry]

Did you try:
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c.Offset(-1)

Positive 1 moves to the next, -1 should move above

Just a thought.

 

[p45cal]

Just a guess; try changing:
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c.Offset(1)
to:
If c.Row <> 1 Then ActiveSheet.HPageBreaks.Add Before:=c