Thread: Insert Pagebreak Insert pagebreak in next row where value matches the value in A1

Copy this code into a standard module. It will loop through the sheets, excluding the ones you named, and look down column-A for another occurence of the value in A1 of each sheet and set a horizontal page break before the row where the value is found.

Option Explicit

Sub insert_Hpagebreak()

    Dim ws As Worksheet, rFound As Range
    
    On Error Resume Next
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Stops1" Or ws.Name <> "Stops1 Summary" Then
            With ws
                .ResetAllPageBreaks
                Set rFound = .Columns("A").Find(what:=.Range("A1").Value, after:=.Cells(2, 1), LookIn:=xlValues, _
                    lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)
                
                If Not rFound Is Nothing Then
                    ws.HPageBreaks.Add before:=rFound
                End If
                    
            End With
        End If
    Next ws

End Sub