There is no 'rule' that can be automated like that. Pagebreaks were placed manually in the original document.
They are put before subject headers, so that a particular subject does not get divided over two pages. While its true there's one particular word that's consistent over all subject headers, we often have 2-3 subjects in one page, completely variant of size - which can differ greatly. There is no way to create a hard rule to specify how often to put a breakpoint on one of these header rows.
What I have done is use code of the format
Do While i <= Sheets(sheet).HPageBreaks.count
i = i + 1
Set currentBreakRow = Sheets(sheet).HPageBreaks(i).Location
If currentBreakRow.EntireRow.Hidden = False Then
....blah blah, do stuff here...
to find each applicable pagebreak in the sheet.
In the rest of the Do While loop, I read cells in all the columns on the row currentBreakRow and search the entire new document for the content in those cells until I find a match for all of them.
This works, but its slow, so I'd like to hear if there's a more effective way.
Bookmarks