Manual page break on certain word
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! |
Manual page break on certain word
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 |
Manual page break on certain word
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 |
Manual page break on certain word
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 |
All times are GMT +1. The time now is 02:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com