Page break after keyword?

[MARK858]

Hi there, I have found quite a lot of code for inserting page breaks before cells or specific values (like the code below for example, provided by Domenic in a previous thread).

I want to do something very similar but my problem is the only unique keyword I can find in the right position needs to be above the page breaks (i.e. after the keyword).

All the other criteria are the same i.e. the word is in a single column (say "A") and it is a single word (might as well stick to "A" as in Dominic's code).

Is this possible?

Option Explicit

Sub test()

    Dim FoundCell As Range
    Dim FirstAddress As String
    Dim PrevAddress As String
    Dim CurrAddress As String
    Dim SearchTerm As String

    SearchTerm = "A"

    With Columns("A")
        Set FoundCell = .Find(SearchTerm, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
        If Not FoundCell Is Nothing Then
            FoundCell.Name = "FirstAddress"
            Do
                PrevAddress = FoundCell.Address
                FoundCell.Resize(3).EntireRow.Insert
                ActiveSheet.HPageBreaks.Add before:=Range(PrevAddress)
                Set FoundCell = .FindNext(FoundCell)
            Loop While FoundCell.Address <> Range("FirstAddress").Address
        Else
            MsgBox "No search term found...", vbExclamation
        End If
    End With
        
End Sub

 

[pbornemeier]

Sub AddPageBreakAfterKeywordInColumnA()
    Dim FoundCell As Range
    Dim FirstAddress As String
    Dim PrevAddress As String
    Dim SearchTerm As String
    SearchTerm = "A"
    RemoveExistingManualPageBreaks
 
    With Columns("A")
        Set FoundCell = .Find(SearchTerm, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
        If Not FoundCell Is Nothing Then
            FoundCell.Name = "FirstAddress"
            Do
                PrevAddress = FoundCell.Address
                ActiveSheet.HPageBreaks.Add before:=Range(PrevAddress).Offset(1, 0)
                Set FoundCell = .FindNext(FoundCell)
            Loop While FoundCell.Address <> Range("FirstAddress").Address
        Else
            MsgBox "No search term found...", vbExclamation
        End If
    End With
End Sub
 
Sub RemoveExistingManualPageBreaks()
    Dim lX As Long
    For lX = ActiveSheet.HPageBreaks.Count To 1 Step -1
        ActiveSheet.HPageBreaks(lX).Delete
    Next
End Sub

 

[MARK858]

Sorry Phil, got wrapped up in a project and forgot I hadn't repled.
All works exactly as requested (All I have to do now (because someone has asked) is work out if you can do the same in word..ah well).
Thanks for all your solution and help

 

[pbornemeier]

Would the word appear alone in a paragraph? Or should the page break trigger no patter where it showed.
It would be easy to do a search and replace by using
Keyword^p as the Find What and
Keyword^m as the Replace With

That would replace the keyword followed by a paragraph mark with the keyword followed by a manual page break

 

[MARK858]

In this case it actually is 3 words "End of report" but it does appear by itself. afraid I am on my phone at present. and so I won't be able to test anything until tonight

 

[MARK858]

Cheers Phil, have got the page breaks sorted.
Now to learn a bit more about Word VBA.
Though I must admit at first look I much prefer Excel VBA, but you never know it might grow on me.