Inserting page breaks

[Rich Mogy]

I have written a macro that will insert page breaks in my workbook everytime
the word "Date:" is found.

How can I eliminate Excel's automatic page breaks and use only my manual
page breaks?

Thank you in advance.

Rich Mogy
Here is the macro:
Sub pagebreaker3()

'

Dim FirstAddress As String


'

Range("A2").Select

Set c = Cells.Find(What:="Date:", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True)
'above saves the address of the find
If Not c Is Nothing Then
FirstAddress = c.Address 'saves first address
Range(c.Address).Select 'selects row with address
Else
Exit Sub
End If
Do Until c.Address = "$A$1"
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
'inserts page break
Set c = Cells.FindNext(c) 'finds next occurrence
Range(c.Address).Select 'select row with next occurrence
Loop
End Sub

 

[Don Guillett]

Try this instead

Sub SetPageBreaks_Don()
With ActiveSheet.Cells
..PageBreak = xlPageBreakNone

Set c = Cells.Find(What:="date", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'MsgBox c.Row
ActiveSheet.HPageBreaks.Add Before:=.Cells(c.Row, 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

 

[Rich Mogy]

That only removes my created page breaks. I'm trying to only have my page
breaks occurr, not Excels default.

Rich

 

[Don Guillett]

Fully tested using your example. If desired, send your wb to my address
below along with very specific instructions and before/after examples.