Checking for Page Breaks Multiple Times

[Farscape2020]

I have created list of data that is divided into separate sections with their own title. For the first section, if the data crosses a page break, I would like to insert some rows and add the headings on top of the page. For the second and third section, if it crosses a page break, I would like to move it to the next page. To detect the horizontal page break, I am using the following code (found on this discussion board).

    Dim HPB As HPageBreak
    Dim pageArray() As Integer
    Dim i As Integer

    i = 0

    For Each HPB In ws.HPageBreaks
        ReDim Preserve pageArray(i)
        pageArray(i) = Right(HPB.Location.Address, Len(HPB.Location.Address) - InStr(2, CStr(HPB.Location.Address), "$", vbTextCompare))
        i = i + 1
    Next

This works well for the first section. However, because the inserted titles has wrapped text which takes up muliple lines, this changes where the page breaks are located. For instance, when I initally run this loop, it states the second horizontal page break is at row 62. When I insert the title row, the page break is moved to row 61. In between fomatting sections, I would like to run this loop again to revaluate where the page breaks are located to make the formatting more accurate. However, when I run this loop, I seem to "randomly" receive the error "run-time error '9': Subscript out of range" and I receive this error each time I run this loop in between formatting sections. When I say I "randomly" receive this error, sometimes it occrus near the beginning, middle, or end when creating my sheets.

Would someone be able guide me to be able to check, multiple times, where the horizontal page breaks are located or or better method to achieve my desired outcome?

 

[Akuini]

Would someone be able guide me to be able to check, multiple times, where the horizontal page breaks are located or or better method to achieve my desired outcome?

I don't quite understand what you're trying to do, but let's start with getting where the horizontal page breaks are located & load it into an array. Something like this:

Dim HPB As HPageBreak
    tx = ""
    For Each HPB In ActiveSheet.HPageBreaks
        tx = tx & "," & HPB.Location.Row
    Next
    ary = Split(Mid(tx, 2), ",")

The array "ary" will capture every horizontal page breaks row.

It would be better if you could upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here. Also, ensure that the link is accessible to anyone.
You can set up an example with two sheets:
Sheet1 contains your existing data.
Sheet2 contains the data as expected.

 

[Shloime]

I have created list of data that is divided into separate sections with their own title. For the first section, if the data crosses a page break, I would like to insert some rows and add the headings on top of the page. For the second and third section, if it crosses a page break, I would like to move it to the next page. To detect the horizontal page break, I am using the following code (found on this discussion board).

    Dim HPB As HPageBreak
    Dim pageArray() As Integer
    Dim i As Integer

    i = 0

    For Each HPB In ws.HPageBreaks
        ReDim Preserve pageArray(i)
        pageArray(i) = Right(HPB.Location.Address, Len(HPB.Location.Address) - InStr(2, CStr(HPB.Location.Address), "$", vbTextCompare))
        i = i + 1
    Next

This works well for the first section. However, because the inserted titles has wrapped text which takes up muliple lines, this changes where the page breaks are located. For instance, when I initally run this loop, it states the second horizontal page break is at row 62. When I insert the title row, the page break is moved to row 61. In between fomatting sections, I would like to run this loop again to revaluate where the page breaks are located to make the formatting more accurate. However, when I run this loop, I seem to "randomly" receive the error "run-time error '9': Subscript out of range" and I receive this error each time I run this loop in between formatting sections. When I say I "randomly" receive this error, sometimes it occrus near the beginning, middle, or end when creating my sheets.

Would someone be able guide me to be able to check, multiple times, where the horizontal page breaks are located or or better method to achieve my desired outcome?

I don't get exactly what "Farscape2020"'s desired outcome is but in my case I wanted page breaks only in blank rows this is the code i used this should be easy to adjust to your desired outcome:

Sub SetPageBreaksOnBlankRows()
Dim ws As Worksheet
Dim PBr As Long
Dim AD As String
Dim PBS As HPageBreaks 'HPageBreaks for Horizontal use VPageBreaks for vertical
Dim HPB As HPageBreak
Set ws = ActiveSheet
Set PBS = ws.HPageBreaks

' this selects the last row see here Page breaks, search manual PageBreaks & time error 9: “Subscript out of Range”
ws.Cells.Find(What:="*", _
after:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Select

For Each HPB In PBS
PBr = HPB.Location.row
'test if page break location row is not blank and 1 row above is not blank then move to the first blank row above it
If Application.WorksheetFunction.CountA(ws.Rows(PBr)) > 0 And Application.WorksheetFunction.CountA(ws.Rows(PBr - 1)) > 0 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ws.Cells(PBr, 1).CurrentRegion
End If
Next

End Sub