Excel 2003 Repeat header and sub-header after page break
Hello Guru's,
UPDATE:
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.
Thanks again.
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.
Stephanie
UPDATE:
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.
Thanks again.
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.
Stephanie
From EE and slightly modified:
Sub InsertPageBreaks()
Dim i As Long, rw As Range
For i = ActiveSheet.HPageBreaks.Count To 1 Step -1
ActiveSheet.HPageBreaks(i).Delete
Next i
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)
End Select
Next rw
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Exactly! You summarized that perfectly.
I have one more favor to ask - when you respond can you please include a line that I can put in the immediate window to see what line number the page break is on? I really do try to learn from the help you provide me by stepping through the code and using the immediate window instead of just cut and pasting the solution :)
Also, you have some time here. I just learned that I don't need this until 2/24 so we have some time to work with this.
Thanks again-
Stephanie
I have one more favor to ask - when you respond can you please include a line that I can put in the immediate window to see what line number the page break is on? I really do try to learn from the help you provide me by stepping through the code and using the immediate window instead of just cut and pasting the solution :)
Also, you have some time here. I just learned that I don't need this until 2/24 so we have some time to work with this.
Thanks again-
Stephanie
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Stellan,
That is awesome, thank you! I will award the points as soon as I transfer it over to the real workbook. I just don't want to close the post until I'm sure I don't have any other questions.
Stephanie
That is awesome, thank you! I will award the points as soon as I transfer it over to the real workbook. I just don't want to close the post until I'm sure I don't have any other questions.
Stephanie
ASKER
Stellan,
I'm sorry to have to report this, but when I run the code I get a run-time error 9 Subscript out of range error. Here's a few things I tried:
First I tried the code in my real book and I got a few different results:
Once it copied a row 6 times - without a subheader.
A few times it copied the same subheader 10 different times with a pagebreak after each one.
So I moved my report over to the sample workbook to see if it behaved differently. It was better, but it generated the Run Time error #9. Here is where it breaks:
Function FirstAutoHPageBreak(wkS As Worksheet) As Range
Dim i As Long
Dim pbsH As HPageBreaks
Set pbsH = wkS.HPageBreaks
For i = 1 To pbsH.Count
If pbsH(i).Type = xlPageBreakAutomatic Then ----- page break = -4105
Set FirstAutoHPageBreak = pbsH(i).Location
Exit Function
End If
Next i
End Function
Both workbooks had the first row as the header row (set through page set) and the reports were landscaped. I made sure the word "Subheader" was in the same column. Does it make any difference if the set print range is set?
I'll keep trying, but if you have any suggestions I would appreciate them.
Stephanie
I'm sorry to have to report this, but when I run the code I get a run-time error 9 Subscript out of range error. Here's a few things I tried:
First I tried the code in my real book and I got a few different results:
Once it copied a row 6 times - without a subheader.
A few times it copied the same subheader 10 different times with a pagebreak after each one.
So I moved my report over to the sample workbook to see if it behaved differently. It was better, but it generated the Run Time error #9. Here is where it breaks:
Function FirstAutoHPageBreak(wkS As Worksheet) As Range
Dim i As Long
Dim pbsH As HPageBreaks
Set pbsH = wkS.HPageBreaks
For i = 1 To pbsH.Count
If pbsH(i).Type = xlPageBreakAutomatic Then ----- page break = -4105
Set FirstAutoHPageBreak = pbsH(i).Location
Exit Function
End If
Next i
End Function
Both workbooks had the first row as the header row (set through page set) and the reports were landscaped. I made sure the word "Subheader" was in the same column. Does it make any difference if the set print range is set?
I'll keep trying, but if you have any suggestions I would appreciate them.
Stephanie
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Stellan,
Sorry for the delay - I had to remove the confidential information. There really isn't much difference
between the dummy records I was using and the sample provided, except the dummy records are longer and have a varienty of comment cells with hard line breaks and word wraps. I did notice on one really long comment cell, the line broke between the row instead of prior to the row (meaning the record appeared on the bottom of page x and top of page y).
I thought of this and was wodering if this might would be worth a try - would it make any difference if I included a blank row under each hearder (on page format, rows to repeat would be 1 and 2 instead of just 1). That would eliminate the always having to insert the repeat sub-header, instead, the code would just look for the last sub-header used and copy it. This would not always be 100% but would be huge improvement for 100% manual insert the users have to do now.
Thank you again and I'm sorry this question has been elevated to to the headache status.
Stephanie
SubHeader-Sample2.xls
Sorry for the delay - I had to remove the confidential information. There really isn't much difference
between the dummy records I was using and the sample provided, except the dummy records are longer and have a varienty of comment cells with hard line breaks and word wraps. I did notice on one really long comment cell, the line broke between the row instead of prior to the row (meaning the record appeared on the bottom of page x and top of page y).
I thought of this and was wodering if this might would be worth a try - would it make any difference if I included a blank row under each hearder (on page format, rows to repeat would be 1 and 2 instead of just 1). That would eliminate the always having to insert the repeat sub-header, instead, the code would just look for the last sub-header used and copy it. This would not always be 100% but would be huge improvement for 100% manual insert the users have to do now.
Thank you again and I'm sorry this question has been elevated to to the headache status.
Stephanie
SubHeader-Sample2.xls
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Stellan, thank you again! I misunderstood the first time about the empty cell and appreciate the second explanation you provided. You put a lot of time in this and it was something I could not do for the users.
Stephanie
Stephanie
Stephanie,
Thank you for your kind words. I am so glad that I could help. Actually I learnt something new which will be useful in my own projects. Of course I am grateful for the points too.
Stellan
Thank you for your kind words. I am so glad that I could help. Actually I learnt something new which will be useful in my own projects. Of course I am grateful for the points too.
Stellan
ASKER
In MS Access, when I add a group header I can select the following options:
Force New Page - No
Keep Together - Yes
Repeat Section - Yes
So in Excel, I'm trying to make my report keep a group of rows together but if they pagebreak due to page size limitations, I want the group header to duplicate itself on the second row of next page (first row is the report header).
So far, the code I've tried all add a pagebreak but do not take into account the number of rows on the page, for example:
GROUP HEADER WITHOUT ANY RECORDS
PAGEBREAK
GROUPHEADER WITHOUT ANY RECORDS
PAGEBREAK
GROUPHEADER
RECORD ONE
RECORD TWO
PAGEBREAK
GROUPHEADER
All of those should fit on one page but instead the code inserts 4 page breaks.
I did find some code that will add a pagebreak every x# of lines, and even if that is integrated into the other code, it still omits the group header being duplicated and the issue that each row is not the same height.
I would appreciate any suggestion you can offer, even if the suggestion is manually add the pagebreaks and copy and paste the group header where needed!
Thanks again,
Stephanie