Macro to Set Page Breaks in Range Named "Journals"

[howard]

I have code below to set page breaks in the range named "Journals", which is S12:X182 on the active sheet. The page breaks should be between 45 and 55 rows afrter S12. However, the page break needs to be one row below text "Journal Check" in Col U if the break is not there based on the above logic

Based on the above the current Breaks should be Row 60, 107, 156, and 182

Kindly amend my code accordingly

 Sub Print_Preview_JournalsTest()
    Dim i As Long, lastRow As Long, journalsRange As Range
    Dim rowCount As Long, journalCount As Long, startRow As Long, endRow As Long
    Dim j As Long
    
    ActiveWindow.View = xlNormalView
    ActiveSheet.ResetAllPageBreaks
    
    With ActiveSheet
        Set journalsRange = .Range("Journals")
        lastRow = .Cells(.Rows.count, "S").End(xlUp).Row
        
        rowCount = 0
        journalCount = 0
        startRow = 12
        
        For i = startRow To lastRow
            If .Cells(i, "U") = "Journal Check" Then
                journalCount = journalCount + 1
                
                If journalCount = 1 And i >= startRow + 45 Then
                    ' If the first Journal Check is not in the first 45 rows,
                    ' find the first row below row 45 that contains Journal Check.
                    For j = i + 1 To lastRow
                        If .Cells(j, "U") = "Journal Check" Then
                            i = j
                            Exit For
                        End If
                    Next j
                End If
                
                If rowCount >= 45 Then
                    endRow = i - 1
                    If .Cells(endRow, "U") <> "Journal Check" Then
                        ' If the last row in the current range does not contain
                        ' Journal Check, find the first row below the range that
                        ' contains Journal Check and set the page break there.
                        For j = i To lastRow
                            If .Cells(j, "U") = "Journal Check" Then
                                endRow = j - 1
                                Exit For
                            End If
                        Next j
                    End If
                    
                    ' Check if the endRow matches the row of the last page break
                    If endRow <> .HPageBreaks(.HPageBreaks.count).Location.Row Then
                        .Cells(endRow + 1, "S").PageBreak = xlPageBreakManual
                    End If
                    
                    rowCount = 0
                    startRow = endRow + 2
                End If
            End If
            rowCount = rowCount + 1
        Next i
    End With
    
    ActiveWindow.View = xlPageBreakPreview
End Sub

 

[howard]

I have also posted on Macro to set Page Breaks