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

Try it like this - it looks like your numbers are in the row below the values in column A: if that is not the case, post back.

Sub InsertPageBreaksBasedOnV()

Dim i As Integer 

Dim r As Range 

With ActiveSheet 

    .ResetAllPageBreaks 

    For i = 2 To Application.Max(.Range("V:V")) 

        Set r = .Range("V:V").Find(i, After:=.Range("V1"), LookAt:=xlWhole) 

        If Not r Is Nothing Then 

            .HPageBreaks.Add Before:=.Cells(r.Row - 1, 1) 

        End If 

    Next i 

End With 

End Sub

Answer 3

Here is my slightly different take:

Sub InsertPageBreaks()
    Dim rng As Range
    Set rng = Range("V15")
    Do
        Set rng = Range("V:V").Find(What:=rng.Value + 1, After:=rng, LookIn:=xlValues, LookAt:=xlWhole)
        If rng Is Nothing Then Exit Do
        ActiveSheet.HPageBreaks.Add Before:=rng.Offset(-1)
    Loop
End Sub

Answer 4

Thanks for the responses Bernie and HansV. I tried each approach and I wasn't seeing the Page Breaks taking effect. Now I'm wondering if it has anything to do with the next step that I still need to figure out about setting the Print Area. Probably not entirely since it should still apply the Page Breaks.

Not sure if me sending an example file is an option to troubleshoot what is going on, but that may help resolve what I'm dealing with.

Answer 5

Thanks Bernie. I responded in line below to HansV's response. Not seeing the expected results I was looking for quite yet.