Page Break code Adjustment needed

[kelly mort]

Hi ,
Instead of hardcoding the number of rows to set the pagebreak into the script, I want to set that dynamically using some criteria:
In column B, I have say x, y, z, etc. they are sorted so all x before all y then all z etc. so I want to set the pagebreak to match the x items then the y items etc.


To put long story short, I wanna have each criteria on a page.


I need some assistance to resolve this.


Thanks
Kelly

Sub HPage_Breaks()
Dim rpp As Long, rh As Long, i As Long, lr As Long, lc As Long
lr = Sheet2.Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = Sheet2.Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    On Error GoTo Cancelled
ActiveWindow.View = xlPageBreakPreview
ActiveWindow.View = xlNormalView


With Sheet2
    .ResetAllPageBreaks
    .PageSetup.Zoom = 50
    .PageSetup.PrintArea = .Range("A2:O" & lr).Address  
    .PageSetup.PrintTitleRows = "$1:$2" 
    .HPageBreaks.Add (.Cells(52, 1))
    For i = 1 To lr / (51)
        .HPageBreaks.Add (.Cells(.HPageBreaks(i).Location.Row + 51, 1))
    Next i
End With
Sheet2.PrintOut
Cancelled:
Exit Sub
End Sub

 

[footoo]

This might help : https://www.extendoffice.com/documents/excel/3425-excel-insert-page-break-when-value-changes.html

 

[kelly mort]

Okay cool. It's working great just that it is separating the headers to a different page.

How do I exclude the headers?

 

[footoo]

Without seeing what code you are using and the layout of your sheet, I am only guessing.
Presumably your page break loop is starting from row 1 which is a header row.
If so, change the loop so that it starts from the first data row.

 

[kelly mort]

Okay done

Thanks