'-------------------------------------------------------------
Option Explicit

Sub setPageBreaks()
Const sSpecialWord = "print"
Const iNbSpecial = 3
Const sSearchColumn = "A"

Dim rCell As Range
Dim iCount As Integer
Dim iLastRow As Long
Dim sFirstFound As String

iLastRow = Cells(Rows.Count, sSearchColumn).End(xlUp).Row
ActiveSheet.ResetAllPageBreaks

Set rCell = Columns(sSearchColumn).Find( _
what:=sSpecialWord, _
after:=Cells(Rows.Count, sSearchColumn), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByColumns, _
searchdirection:=xlNext, _
MatchCase:=False)
If rCell Is Nothing Then
MsgBox sSpecialWord & " not found"
Exit Sub
End If
sFirstFound = rCell.Address
iCount = 1
Do
Set rCell = Columns(sSearchColumn).FindNext(after:=rCell)
iCount = iCount + 1
If iCount = 3 Then
ActiveSheet.HPageBreaks.Add rCell
iCount = 0
End If
Loop Until rCell.Address = sFirstFound
End Sub
'------------------------------------------------------------

HTH
--
AP
"JAB" a écrit dans le message de news:
...
Also, I need the page break to occur BEFORE the word "print", rather than
after.

Thank you,
JB

"Ardus Petus" wrote:

Try:
For lRow = 1 + lPageLength To .UsedRange.Rows.Count Step lPageLength
(add a dot before UsedRange).
If it does not work, please post back

--
AP

"JAB" a écrit dans le message de news:
...
When I go to run it, it says, "run-time error '424'", "object required"
,
then it points me to "For lRow = 1 + lPageLength To
UsedRange.Rows.Count
Step
lPageLength"

I have no idea where to go from here.

JB

"Ardus Petus" wrote:

Sub setPageBreaks()
Const lPageLength As Long = 85
Dim lRow As Long
ActiveWindow.View = xlPageBreakPreview
With ActiveSheet
.ResetAllPageBreaks
For lRow = 1 + lPageLength To UsedRange.Rows.Count Step
lPageLength
.HPageBreaks.Add befo=Rows(lRow)
Next lRow
End With
ActiveWindow.View = xlNormalView
End Sub

HTH
--
AP

"JAB" a écrit dans le message de news:
...
I need to have a page break every 85th row. I need this done
automatically,
because I have over 3800 rows. Any help is appreciated.

JB