Find and Move Last hpagebreak

[mharper90]

I have a 3 row tall signature block that is inserted 4 rows below the bottom of a dynamic table. I want to insert code that will ensure that the signature block never gets printed between 2 pages.

I'm looking for code that will find the location of the last hpagebreak, compare it to the location of the last row on the sheet (the bottom of the signature block), and if the difference between those locations is less than 4 rows, then it would relocate the last hpagebreak up the page by 4 rows.

This way, if the entire signature block doesn't fit at the bottom of the last page of the table, then it will be forced to print intact at the top of the next page instead.

 

[John_w]

Try this macro:

Public Sub Move_Last_Page_Break()

    Dim saveActiveCell As Range
    Dim lastRow As Long
    Dim lastPageBreak As HPageBreak
        
    Set saveActiveCell = ActiveCell
    
    With ActiveWorkbook.ActiveSheet
        
        'Select last cell so that Excel recalculates automatic page breaks. Can prevent "Subscript out of range" error when referring to HPageBreaks
        'https://support.microsoft.com/en-us/help/210663/you-receive-a-subscript-out-of-range-error-message-when-you-use-hpageb
        
        lastRow = .UsedRange.Rows.Count - .UsedRange.Row + 1
        .Cells(lastRow, "A").Select
        
        Set lastPageBreak = .HPageBreaks(.HPageBreaks.Count)
        If lastRow >= lastPageBreak.Location.Row And lastRow - lastPageBreak.Location.Row < 2 Then
            If lastPageBreak.Type = xlPageBreakAutomatic Then lastPageBreak.Type = xlPageBreakManual
            lastPageBreak.Delete
            .HPageBreaks.Add before:=.Cells(lastRow - 2, "A")
            MsgBox "Last page break moved to above row " & lastRow - 2
        Else
            MsgBox "Last page break not moved from above row " & lastPageBreak.Location.Row
        End If
        
    End With
    
    saveActiveCell.Select
    
End Sub