VBA to Insert Page Breaks that will Keep related Items Together

[jhabey01]

I have been thinking about this for awhile, and I am not sure where to even begin. I have a report like the attached that I produce every month.

I would like to keep the information that pertains to the Group Name together on the same page. The last row pertaining to any group is the Group Name Total Row. What happens currently is a page breaks will not keep the information pertaining to the group together. A few notes is that the number of groups on any given month can vary, so it may not be the same 14 Groups.
Thx

 

[SkipVought]

Hi,

What code do you have so far? Where are you stuck?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

Looks to me that you can:
1. Start in row 4, column J (Total Premium)
2. Loop down the rows in that column until you hit an empty cell
3. Insert a Page Break
4. Repeat steps 1-2-3
until you get to the point of having empty cell-data-empty cell (that will be your last row of data, Grand Total). You can count how many cells with data you have step thru to detect your "Grand Total" row.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[jhabey01]

Hello,
I am stuck on the approach I want to take, I know what I want but not sure how to get there conceptually. I may not need code here persay but some direction on solving this.

@ Andrzejek will this approach put a client on each page or can I group a number of clients together until it realizes that if another group was added it would go over the page break and then therefore it would be moved tot he next page.

Thanks,
John

 

[SkipVought]

Conceptually:

Here's what I often do to construct a framework for a process. Go to your sheet and change the view to the Page Break View.

Reduce the zoom. You will see dotted blue lines where SOFT page breaks will occur with the current page setup when you prin preview or print.

You can drag any horizontal PB upward and the line becomes a solid blue line representing a HARD PB.

You can turn on you macro recorder and record setting one of thos hard breaks. Turn off the recorder and take a look at your code. It will be the basis for setting your PBs.

You'll want to also write a short routine to CLEAR all HARD PBs.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[macropod]

Try:

Sub PrintFormat()
Application.ScreenUpdating = False
Dim i As Long
With ActiveSheet
    .UsedRange
    .ResetAllPageBreaks
    .PageSetup.Zoom = 65
    For i = 5 To .Cells.SpecialCells(xlCellTypeLastCell).Row
        If Trim(Cells(i, 10).Value) = "" Then
            Cells(i, 1).PageBreak = xlPageBreakManual
        End If
    Next
End With
Application.ScreenUpdating = True
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[Andrzejek]

>@ Andrzejek will this approach put a client on each page
Yes. (if by 'client' you mean data in Column B "Group Name")

>or can I group a number of clients together until it realizes that if another group was added it would go over the page break and then therefore it would be moved tot he next page.

Well, you could do that, too.
But first you would have to clearly define "group [] of clients"
In your example I can see the same name in "Carrier Name" (column A) with different names in "Group Name" (Column B)

It would help if you can present the sample of data that is showing your actual situation (with group of clients, and such) and highlight the rows where you want the Page Breaks to be inserted.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Is the requirement to only have one group per page, no matter how small the group or to have no page where a group is split between two pages?

Of course all bets are off if a group exceeds a page.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[jhabey01]

The requirement is to have no page break where the group name is the same. I am looking to not have a group split between two pages.
The page break has to be coded such that it keeps all of the Clients with in the row titled GroupName together on one page.
I would prefer to be able to have multiple groups (clients) on a page.
* I understand that if a group name (client) exceeds more than one page all bets are off.



Andrzejek (Programmer) 6 May 16 12:45

>@ Andrzejek will this approach put a client on each page
Yes. (if by 'client' you mean data in Column B "Group Name")

>or can I group a number of clients together until it realizes that if another group was added it would go over the page break and then therefore it would be moved tot he next page.

Well, you could do that, too.
But first you would have to clearly define "group [] of clients"
In your example I can see the same name in "Carrier Name" (column A) with different names in "Group Name" (Column B)

----> All of the Clients listed in Column B belong to the same Carrier

It would help if you can present the sample of data that is showing your actual situation (with group of clients, and such) and highlight the rows where you want the Page Breaks to be inserted.

---> this is actual sample data obviously the names are changed to protect the innocent but this is what I got and work with

 

[SkipVought]

Well did Paul's solution solve your problem?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[macropod]

Here's a more sophisticated approach, to minimise the page count:

Sub PrintFormat()
Application.ScreenUpdating = False
Dim i As Long, j As Long, k As Long, StrRows As String
With ActiveSheet
    .UsedRange
    .ResetAllPageBreaks
    'Scale to 1 page wide.
    .PageSetup.FitToPagesWide = 1
    .PageSetup.FitToPagesTall = False
    'Insert manual page breaks at every blank line.
    For i = 5 To .Cells.SpecialCells(xlCellTypeLastCell).Row
        If Trim(Cells(i, 10).Value) = "" Then
            .Cells(i, 1).PageBreak = xlPageBreakManual
            StrRows = StrRows & "," & i
        End If
    Next
    'Progressively delete manual page breaks and check whether the page count decreases.
    ' If not, we've split a record set, so reinstate the manual page break.
    For i = 1 To UBound(Split(StrRows, ","))
      j = ExecuteExcel4Macro("GET.DOCUMENT(50)")
      k = Split(StrRows, ",")(i)
      .Cells(k, 1).PageBreak = xlNone
      If ExecuteExcel4Macro("GET.DOCUMENT(50)") = j Then
        .Cells(k, 1).PageBreak = xlPageBreakManual
      End If
    Next
End With
Application.ScreenUpdating = True
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

Here is a solution that accommodates Group Name ranges that exceed a page worth of rows. The process finds the next soft/automatic Page Break and then traverses upward to find the first cell in the Group for that name, where it Adds a Horizontal Page Break. Adding a HPB automatically resets all the following soft/automatic PBs below it. And the process repeats to then end of the table. It has been tested on your data and I modified the Page Layout to decrease the printable row area, thereby increasing the number of PBs, causing an instance of rows of a Group Name exceeding rows on a page.
[pre]
Sub RePaginate()
'SkipVought 2016 May 8
'put as many Groups on one page. No Group to Split between two pages unless rows for Group exceed one page
'-------------------------------------------
'Process:
'1 find the next soft Page Break
'2 traverse Group Name range rows upward to cell before blank cell
'3 Add a Horizontal Page Break

Dim rFound As Range 'used to find Group Name to start table analysis
Dim r As Range 'used to loop thru Group Name range
Dim sPrevGroup As String 'used to prevent indefinite loop for Groups exceeding one of more pages

With ActiveSheet
.ResetAllPageBreaks
With .UsedRange
Set rFound = .Cells.Find("Group Name")
If Not rFound Is Nothing Then
'--traverse Group Name to find soft Page Break
For Each r In Range(rFound, .Cells(.Rows.Count, rFound.Column))
If r.EntireRow.PageBreak = xlPageBreakAutomatic Then 'FOUND soft Page Break
If sPrevGroup <> r.Value Then 'determine if this Group already has a Horizontal PB
If Trim(r.Offset(-1).Value) = "" Then 'is this the first cell in the Group
r.Select 'select this cell
ActiveSheet.HPageBreaks.Add Before:=ActiveCell
Else 'otherwise
r.End(xlUp).Select 'end.up to select first cell in Group
ActiveSheet.HPageBreaks.Add Before:=ActiveCell
End If
End If
sPrevGroup = r.Value 'store Group Name that has HPB
End If
Next
End If
End With
End With
End Sub
[/pre]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[jhabey01]

Hello, I think that Paul's code is very close. I took a look at it this afternoon and it works when the report is only at most two pages. What I mean is that what if I had 40 Group names, the code keeps all of the Group Names on page one, but on the remaining pages the page print is not keeping the Group name together on pages 2 - the remaining.

I am going to spend some time seeing if I can't come up with something to add to the code.

John

 

[SkipVought]

???

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

In the file you attached with your data in Excel,
Are you expecting to have Page Breaks in empty cells in column J ?

I.e. Page Breaks in rows: 11, 14, 21, 31, 34, 41, 59, 65, 73, 76, 83, 90, 97, 105, (and maybe) 107 ?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[macropod]

The first macro I posted works fine for splitting the report by group. The second one, for some reason, behaves inconsistently; sometimes it paginates correctly; other times not (especially on the first pass) - with the OP's workbook.

Cheers
Paul Edstein
[MS MVP - Word]

 

[jhabey01]

@ Andy, no I do not want a page break at every new group name.
I want to keep the groups or together on a page and only go to a new page if the group can be kept together, Please see earlier explanation

I think Paul understands best and his code seems to work fine only if I have a small number of groups that would not exceed to pages anyway. When I added more groups as a test it did not paginate correctry after the 2nd page that is why I am looking at the code to see if I can add to it.

 

[SkipVought]

Is my code solution invisible?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[jhabey01]

Thanks for all the posts on this got everything working just fine