Hello,
I need to page break on each row containing a specific word in Col. A. I've
tried different code to accomplish this but it didn't work. I'm getting an
"object required" error which doesn't surprise me as I'm not very good at
this. Any and all help would be greatly appreciated; please be specific as
I'm rather a dolt. Thanks!

Maybe something like:

Option Explicit
Sub testme()
Dim myRng As Range
Dim FoundCell As Range
Dim FirstAddress As String

With Worksheets("sheet1")
.ResetAllPageBreaks 'remove them all to start
With .Range("a:a") 'what was selected??
Set FoundCell = .Find(What:="whatgoeshere", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
Lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'not found on the sheet
Else
FirstAddress = FoundCell.Address
Do
If FoundCell.Row 1 Then
.Parent.HPageBreaks.Add Befo=FoundCell
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
End With
End With
End Sub




cottage6 wrote:

Hello,
I need to page break on each row containing a specific word in Col. A. I've
tried different code to accomplish this but it didn't work. I'm getting an
"object required" error which doesn't surprise me as I'm not very good at
this. Any and all help would be greatly appreciated; please be specific as
I'm rather a dolt. Thanks!

--

Dave Peterson

Dave,
The code works great! I can't thank you enough and I'm sure I'll be using
it in more than one spreadsheet, so you've really helped me out.

"Dave Peterson" wrote:

Maybe something like:

Option Explicit
Sub testme()
Dim myRng As Range
Dim FoundCell As Range
Dim FirstAddress As String

With Worksheets("sheet1")
.ResetAllPageBreaks 'remove them all to start
With .Range("a:a") 'what was selected??
Set FoundCell = .Find(What:="whatgoeshere", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
Lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'not found on the sheet
Else
FirstAddress = FoundCell.Address
Do
If FoundCell.Row 1 Then
.Parent.HPageBreaks.Add Befo=FoundCell
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
End With
End With
End Sub




cottage6 wrote:

Hello,
I need to page break on each row containing a specific word in Col. A. I've
tried different code to accomplish this but it didn't work. I'm getting an
"object required" error which doesn't surprise me as I'm not very good at
this. Any and all help would be greatly appreciated; please be specific as
I'm rather a dolt. Thanks!

--

Dave Peterson

Dave, thanks a million for this! I also had the same problem.

"Dave Peterson" wrote:

Maybe something like:

Option Explicit
Sub testme()
Dim myRng As Range
Dim FoundCell As Range
Dim FirstAddress As String

With Worksheets("sheet1")
.ResetAllPageBreaks 'remove them all to start
With .Range("a:a") 'what was selected??
Set FoundCell = .Find(What:="whatgoeshere", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
Lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'not found on the sheet
Else
FirstAddress = FoundCell.Address
Do
If FoundCell.Row 1 Then
.Parent.HPageBreaks.Add Befo=FoundCell
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
End With
End With
End Sub




cottage6 wrote:

Hello,
I need to page break on each row containing a specific word in Col. A. I've
tried different code to accomplish this but it didn't work. I'm getting an
"object required" error which doesn't surprise me as I'm not very good at
this. Any and all help would be greatly appreciated; please be specific as
I'm rather a dolt. Thanks!

--

Dave Peterson

Glad it worked.

William Elerding wrote:

Dave, thanks a million for this! I also had the same problem.

"Dave Peterson" wrote:

Maybe something like:

Option Explicit
Sub testme()
Dim myRng As Range
Dim FoundCell As Range
Dim FirstAddress As String

With Worksheets("sheet1")
.ResetAllPageBreaks 'remove them all to start
With .Range("a:a") 'what was selected??
Set FoundCell = .Find(What:="whatgoeshere", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
Lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'not found on the sheet
Else
FirstAddress = FoundCell.Address
Do
If FoundCell.Row 1 Then
.Parent.HPageBreaks.Add Befo=FoundCell
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
End With
End With
End Sub




cottage6 wrote:

Hello,
I need to page break on each row containing a specific word in Col. A. I've
tried different code to accomplish this but it didn't work. I'm getting an
"object required" error which doesn't surprise me as I'm not very good at
this. Any and all help would be greatly appreciated; please be specific as
I'm rather a dolt. Thanks!

--

Dave Peterson



--

Dave Peterson