Insert Dynamic Page Breaks with VBA for Excel Macro

Question

I'm running into another roadblock for my VBA knowledge related to a previous post that got resolved late last week.

Excel VBA Cell/Range Selection Loop - Microsoft Community

In the same file that I'm working on cleaning up there are 'custom' page breaks that need to be added based on a value change in a specific column. In column V, we have what is called a 'Bin Group'. When that group number transitions, then I need to insert a page break so that a printed out packet will only contain records for a single Bin Group. The team dealing with this will print the whole sheet, but parse out the paperwork based on Bin Group for multiple users to go perform the actions associated with the data. Generally, the Bin Group values are 9 rows apart, but there are some scenarios where they have more rows between. Then the page breaks need to occur based on Column A being populated.

Bin Group and its values are in Column V. V15 is the first record in the file and should be consistent for all extracts that the Macro will need to run over. The additional rows would come after the Bin (see Column D, Row 20) to extend the distance from each Bin Group indicator.

I feel like it may have some code resembling what was done to solve my previous issue, but things get a little more complex needing to compare values and apply the page break to a different column/row than the comparisons are being done. Screen shots below hopefully will describe the data and what I'm trying to do.

Below, the goldish line is where the Page Break is needed as the Bin Group value changes/transitions from 1 to 2. The purple rectangles above signify the grouping of data that needs to stay together, so the page breaks need to occur prior to the next Part Number (in Column A).

Hopefully, I've provided enough detail to get some suggestions of how to proceed or how to potentially go about a solution. I've struck out on my online searches to this point.

Thanks in advance.

Answer 1

I used constants in column V to identify the values, so it does not pick up blanks per se. You could try adding a page break after the last constant is found
(try it out and adjust the 9 to get the break in the right place)

Sub InsertPageBreaksBasedOnV2()

Dim i As Integer

Dim r As Range

With ActiveSheet

.ResetAllPageBreaks

For i = 3 To .Range("V:V").SpecialCells(xlCellTypeConstants).Areas.Count

If .Range("V:V").SpecialCells(xlCellTypeConstants).Areas(i).Cells(1).Value <> _

.Range("V:V").SpecialCells(xlCellTypeConstants).Areas(i - 1).Cells(1).Value Then

.HPageBreaks.Add Before:=.Range("V:V").SpecialCells(xlCellTypeConstants).Areas(i).Offset(-1)

End If

Next i

.HPageBreaks.Add Before:=.Range("V:V").SpecialCells(xlCellTypeConstants).Areas(i-1).Offset(9) ' Not sure of the offset here

End With

End Sub

Answer 2

Thanks Bernie. I adjusted the Offset to 7. It worked on the report that I'd shared via OneDrive. But it didn't on the next one. Not sure what the deal is there, things appear to be the same exact scenario (7 rows apart), but no Page Break inserted around that area even. I'm not too worried about that for now. I've got something pretty dialed in now for my colleagues to work with and see what types of issues they have (if any) to come back with "this is nice, but wouldn't it be awesome if...".

#powerofcommunity