Hi,

I have a long report with several lines on worksheet "Sheet1".
I would like to have a code that would insert a page break every time
there is the word "Break" on column C.
Note that the page break would be just above the line indicating
"Break".

Thanks in advance for your help.

Sub dopagbreaks()
ActiveSheet.ResetAllPageBreaks

With Range("c1:c500")
Set c = .Find("Break", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
ActiveSheet.HPageBreaks.Add Befo=Rows(c.Row)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub



On Oct 14, 12:28*pm, Norvascom wrote:
Hi,

I have a long report with several lines on worksheet "Sheet1".
I would like to have a code that would insert a page break every time
there is the word "Break" on column C.
Note that the page break would be just above the line indicating
"Break".

Thanks in advance for your help.

hi Norvascom,

Sub AddHPageBreaks()
For i = 1 To Range("C65536").End(xlUp).Row
If Range("C" & i) = "Break" Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Range("C" & i)
Next
End Sub

Sub DeleteHPageBreaks()
For i = ActiveSheet.HPageBreaks.Count To 1 Step -1
ActiveSheet.HPageBreaks(i).Delete
Next
End Sub


--
isabelle




Le 2011-10-14 13:28, Norvascom a écrit :
Hi,

I have a long report with several lines on worksheet "Sheet1".
I would like to have a code that would insert a page break every time
there is the word "Break" on column C.
Note that the page break would be just above the line indicating
"Break".

Thanks in advance for your help.

for a large data range, the macro will be more faster by using "Find... Next"


--
isabelle