ExcelBanter - Auto Page Break

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Auto Page Break (https://www.excelbanter.com/excel-worksheet-functions/90647-auto-page-break.html)

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

I am sure this isn't the "right" way to do it, but I think it should
work.

I just created a long spreadsheet, went into Page Break View, and moved
the first dotted horizonal line to line 85.

I then scrolled down, and now the break is on every 85th row (85, 170,
255, 340, ect ext)

Anyway...hope this helps.

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

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

'-------------------------------------------------------------
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

Thats amazing...I wish I could program like that! Thank you very much!!!
JB

"Ardus Petus" wrote:

'-------------------------------------------------------------
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