Setting Manual Page Breaks

[kylegjordan]

I'm trying to add page breaks above rows meeting a certain condition and are identified by a for-next loop with an if-then statement that confirms the. This means that the "worksheets.rows().pagebreak = xlpagebreakmanual" uses a variable for the rows argument (code below). Not only is the pagebreak not being set, but the worksheet insists on setting it's own automatic breaks that won't remove when I set code to do so. Please let me know if you see where I'm going wrong, or if you have a different way of approaching this.


Worksheets(1).Rows(58).PageBreak = xlPageBreakNone
Worksheets(1).Columns("I&quot.PageBreak = xlPageBreakManual
For pgb = 1 To 75
If Worksheets(1).Cells(pgb, 8).Value = "cell end" Then
Worksheets(1).Rows(pgb).PageBreak = xlPageBreakManual
End If
Exit For
Next pgb

Thanks.

kj

 

[jebry]

Hi!

You need to remove the Exif For. Excel is only checking the first row cell end.

hth
Jeff Bridgham

 

[kylegjordan]

Didn't work. My pagebreaks aren't being set and excel still insists on adding it's own pagebreaks. Thanks though.

kj

 

[Stoffel24]

Hi Kyle

I was searching for help on page breaks in excel. This code does seem to work although I have on occasion had some odd things happening. I am going to post a query myself so keep an eye out for the replies.

Sub PageSetup()
'** Here we manually set a page break and place column headings in.
Dim intRw As Integer
Dim Found As Boolean
Dim Cnter2 As Integer
Dim PgeBrk As Excel.HPageBreak

ActiveWindow.View = xlPageBreakPreview 'This appears to be important.
For Each PgeBrk In Application.ActiveSheet.HPageBreaks

PgeBrk.Location.Select
'Do some things here and find a new location to add page break.

Worksheets("Tensile-Bend&quot.Rows(ActiveCell.Row + 1).PageBreak = xlPageBreakManual
Next PgeBrk
ActiveWindow.View = xlNormalView

End Sub