I've worked on this some and have added the code I found on EE for adding page breaks (see below). What I'm thinking, and this is more of a question then statement, is that I could add some code in here to do a line count between the word "SUBHEADER". If the row count is 0, then the first occurrence of the subheader will be deleted. If a pagebreak was added, then I need to check to see if the row above has a "SUBHEADER" if not, then find the last one and copy it and insert it.
Can you please give me some guidance as to what I need to use? For example, to check the line before, I think I need to use OFFSET, but I'm clueless what to look for to see if a pagebreak is on the worksheet.
My users are getting spoiled with your solutions to their requests. I tell them I couldn't do it without help (a lot of expert help!)
Today's challenge is repeating a sub-header after a page break. The problem is the header is not in a consistent format. However, I am ok with adding some sort of flag in the sub-header row to trigger VBA code to look for it.
I have included a workbook with two speadsheets to explain what I'm trying to do. The first speadsheet is the raw data. The second spreadsheet is to show what the final printed report should like like. I'll try to summarize it but I think the spreadsheet tells the story.
I have a report on a spreadsheet that has multiple sub-headers.
Under each sub-header are an undetermined number of rows/records - may be even 0.
Each row has a cell for a comment. The users like to max out this cell so the cell may have a 1 line comment or a 30 line comment. The row is never a consistent height.
When the page breaks, the new page needs to repeat the sub-header on the 2nd line of the new page. I know how to do this in Access, but the only way I know how to do this in Excel is to go into page break view and manually insert the sub-header and force a new page break.
Is there a way to do this in VBA?
Thank you again for your expert recommendations and solutions.
From EE and slightly modified:
Dim i As Long, rw As Range
For i = ActiveSheet.HPageBreaks.Count To 1 Step -1
For Each rw In ActiveSheet.UsedRange.Rows
Select Case rw.Cells(1, 1).Text
Case "SUBHEADER", "b", "d"
ActiveSheet.HPageBreaks.Add Before:=rw.Cells(1, 1)