Inserting page breaks

  • Thread starter Thread starter Rich Mogy
  • Start date Start date
R

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
 
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
 
That only removes my created page breaks. I'm trying to only have my page
breaks occurr, not Excels default.

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