Inserting page breaks
 

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 Befo=ActiveCell
'inserts page break
Set c = Cells.FindNext(c) 'finds next occurrence
Range(c.Address).Select 'select row with next occurrence
Loop
End Sub

Inserting page breaks
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rich Mogy" wrote in message
...
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 Befo=ActiveCell
'inserts page break
Set c = Cells.FindNext(c) 'finds next occurrence
Range(c.Address).Select 'select row with next occurrence
Loop
End Sub

Inserting page breaks
 

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

Rich
"Don Guillett" wrote in message
...

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rich Mogy" wrote in message
...
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 Befo=ActiveCell
'inserts page break
Set c = Cells.FindNext(c) 'finds next occurrence
Range(c.Address).Select 'select row with next occurrence
Loop
End Sub

Inserting page breaks
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rich Mogy" wrote in message
...
That only removes my created page breaks. I'm trying to only have my page
breaks occurr, not Excels default.

Rich
"Don Guillett" wrote in message
...

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rich Mogy" wrote in message
...
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 Befo=ActiveCell
'inserts page break
Set c = Cells.FindNext(c) 'finds next occurrence
Range(c.Address).Select 'select row with next occurrence
Loop
End Sub