Insert page break

[Norvascom]

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.

 

[Don Guillett]

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 Before:=Rows(c.Row)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

 

[isabelle]

hi Norvascom,

Sub AddHPageBreaks()
For i = 1 To Range("C65536").End(xlUp).Row
If Range("C" & i) = "Break" Then ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=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 :

 

[isabelle]

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