Page break code

[mkurasinski]

Lets say I have an excel doc that has 6 colums and 1500 rows and
Column B is called file seq. I want a page break to happen everythime
the number in that row within that column is a 1. example Column B
has 10 rows 1.2.3.4.5.1.2.3.4.5.1 everytime 1 is there insert a
pagebreak.

 

[Dave Peterson]

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Set wks = ActiveSheet

With wks
.ResetAllPageBreaks 'maybe???
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
With .Cells(iRow, "A")
If .Value = 1 Then
.PageBreak = xlPageBreakManual
End If
End With
Next iRow
End With
End Sub

 

[arjen van...]

A slightly different solution using a For/Next loop. It works for me.

Sub InsertPageBreaks()

Dim FindRange As Range

With Sheets("Sheet1")
Set FindRange = .Range(.Range("B2"), .Range("B2").End(xlDown))
End With

Dim Cell As Object
Dim q As Long

Worksheets("Sheet1").ResetAllPageBreaks

For Each Cell In FindRange
If Cell.Value = 1 Then
q = Cell.Row
Sheets("Sheet1").Rows(q).PageBreak = xlPageBreakManual
End If

Next Cell

End Sub