VBA Code needed to automate page break(s) based on 3-column groupings

Bump - No response

Bump - No Response

Admin - please review and let me know whether or not I filed/posted correctly. My original post was on 6/27 and I haven't received any responses yet. Thanks.

This is for Horizontal Page Breaks.
Maybe change it for Vertical Page Breaks.

Sub Set_Page_Breaks()
    Const lNumberOfRowsPerSheet As Integer = 30     '<---- Change to your amount of lines per sheet
    Dim l As Long
    ActiveSheet.ResetAllPageBreaks
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A" & lNumberOfRowsPerSheet + 9)     '<---- 9 equals Title Rows + 1
    For l = lNumberOfRowsPerSheet + 9 To ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row Step lNumberOfRowsPerSheet
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A" & l)
    Next
    With ActiveSheet.PageSetup
        .PrintArea = ActiveSheet.UsedRange.Address
        .PrintTitleRows = "$1:$8"     '<----- Change to the Title Rows in your document
    End With
End Sub

Thanks jolivanes for the reply!

This will work for a designated number of rows, but I don't always know how many columns there will be (sometimes there are single columns as well, which represent a single column discount), to be able to add a vertical page break after that number of columns is reached. I was thinking if I just count the number of "fees" columns, regardless of whether any of these singles columns existed, then I could get the desired page breaks.

Any other suggestions? Thanks for reviewing.

Actually, you don't want to use just

Activesheet.rows(RowCnt).cells

because it will take forever to run. Need to limit it. Maybe use

intersect(UsedRange,rows(rowcnt))

Might be some other minor issues. I'll post something more shortly.

Great - thanks! I will give this a try and let you know how it works for me.

This should work if you set up your sheet so you don't have to fiddle with "sometimes single columns".

Sub Set_Page_Breaks_B()
    Const lNumberOfColumnsPerSheet As Integer = 9     '<---- Change to your amount of columns per sheet
    Dim c As Long, LastColumn As Long
    LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    ActiveSheet.ResetAllPageBreaks
    ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=Cells(1, lNumberOfColumnsPerSheet + 2)     '<---- 2 equals Title Columns + 1
    For c = lNumberOfColumnsPerSheet + 2 To LastColumn Step lNumberOfColumnsPerSheet     '<----- Change 2 to 3 if 2 Title Columns in your document
        ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=Cells(1, c)
    Next c
    With ActiveSheet.PageSetup
        .PrintArea = ActiveSheet.UsedRange.Address
        .PrintTitleColumns = "$A:$A"     '<----- Change to the Title Columns in your document
    End With
End Sub

Thanks jolivanes. This is almost what I need. I really need to figure out how to keep the three column groupings together (Qty/Rate/Amount), since not having them together is not ideal for client viewing. (Note: once all formatting is complete, I save the file to pdf and that is what gets sent to the client.) Below is an update to the code, which includes additional formatting, as well as to horizontal page breaks.

What you have sent over so far is great! Any other ideas on how to incorporate the single discount column(s) into the process?

Sub Set_Page_Breaks_B()
    Const lNumberOfColumnsPerSheet As Integer = 15     '<---- Change to your amount of columns per sheet
    
    Dim c As Long, LastColumn As Long
    
    LastColumn = Cells.find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    ActiveSheet.ResetAllPageBreaks
    ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=Cells(lNumberOfColumnsPerSheet + 5)     '<---- 2 equals Title Columns + 1
    
    For c = lNumberOfColumnsPerSheet + 5 To LastColumn Step lNumberOfColumnsPerSheet     '<----- Change 2 to 3 if 2 Title Columns in your document
        ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=Cells(c)
    Next c
    
'    With ActiveSheet.PageSetup
'        .PrintArea = ActiveSheet.UsedRange.Address
'        .PrintTitleColumns = "$A:$D"     '<----- Change to the Title Columns in your document
'    End With

    ' Check to see which version of Office is running.  "PrintCommunication" command only works on Office 2010 or greater.
    ' If Office 2010 or greater (version 14), then turn off print communication to speed up the code when modifying page setup.
    If Val(Application.Version) >= 14 Then Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = "$A:$D"
            .TopMargin = Application.InchesToPoints(1.3)
            .Orientation = xlLandscape
            .PaperSize = xlPaperLetter
            .Zoom = 47
        End With
    ' Turn print communication back on
    If Val(Application.Version) >= 14 Then Application.PrintCommunication = True
        
        ' Turn on Page Break Preview to adjust horizonal page breaks
        ActiveWindow.View = xlPageBreakPreview
            ' If Horizontal Page Break count not = 0 then proceed.
            If HPBcnt <> 0 Then
            Set rng = Range("A" & LROffset)
            Set Hrng = Worksheets(1).HPageBreaks(1).Location
                ' If Horizontal Page Break (HPB) row is less than bottom of Print Area, then move HPB down to bottom, else move it up.
                If Hrng.Row < rng.Row Then
                    ActiveSheet.HPageBreaks(1).DragOff Direction:=xlDown, RegionIndex:=1
                Else
                    ActiveSheet.HPageBreaks(1).DragOff Direction:=xlUp, RegionIndex:=1
                End If
            End If
        ' Turn off Page Break Preview
        ActiveWindow.View = xlNormalView

End Sub

In your example from post#1, I don't see a discount Column anywhere.
Also, now you show in your code that the Title Columns are 4 Columns, not just a single Column.

I omitted that originally because I was thinking the counting of the 3-columns would disregard whether or not a discount column existed. I don't always have discounts for a particular client, so I apologize for leaving that out. As for the title columns, I left out that there were four because I was trying to simplify the example (in your code example, I was able to easily modify and expand to include all 4 title columns.)

Using the constant '1NumberOfColumnsPerSheet' in your sample code and setting that to 15 gets me most of the way there and works fine if the client doesn't have any applicable discounts that month. Thanks again for your help with this problem.

Thanks. It definitely took forever to run with the initial code. I modified to use the 'intersect' limitation indicated in your last post, but am now getting a "run-time error '424': Object required" error.

Originally Posted by mkhammers

Thanks. It definitely took forever to run with the initial code. I modified to use the 'intersect' limitation indicated in your last post, but am now getting a "run-time error '424': Object required" error.

Maybe try changing the code that sets the page break. I seem to recall having some issues with using the code supplied by the macro recorder. Try

ActiveSheet.VPageBreaks.Add Before:=[your variable name here]

If that doesn't fix it then maybe run in debug and find what line fails.

As noted in my previous message though, even modified code will run forever becase once the hard break is added, it still shows up as a soft break so it keeps adding a break to the same column.

I can't get this to work. It seems that horizontal page breaks and vertical page breaks don't have the same value. I did a bunch of testing and for horizontal page breaks, a soft break = 4105, after setting a hard break, it changes to 4135. For columns, after setting a hard break, it stays at 4105.

The following code should work but unfortunately it doesn't. This is based on your original file posting. Code could be added to make this work but I don't have time now. Just remember where the last soft break was found and continue one column to the left.

Dim item As Range
Dim BreakCell As Range
Dim RowCnt As Integer
Dim BreakFound As Boolean




With ActiveSheet.UsedRange

     RowCnt = .Find(What:="Qty", LookIn:=xlValues).Row
    
End With



BreakFound = True




Do While BreakFound
    BreakFound = False


    For Each item In Intersect(Rows(RowCnt), UsedRange)
        If item.Text = "Qty" Then Set BreakCell = item
        If item.EntireColumn.PageBreak = xlAutomatic Then
            'page break found
            BreakFound = True
            ActiveSheet.VPageBreaks.Add Before:=BreakCell
            Exit For
        End If
    Next
    
Loop

If you care to look at how page breaks are identified you can use these.

Private Sub GetRowBreak()

MsgBox Selection.EntireRow.PageBreak

End Sub
Private Sub GetColBreak()

MsgBox Selection.EntireColumn.PageBreak

End Sub

Actually, after a little more testing, this actually isn't too bad.

The following will work to force breaks only prior to the Qty column in your original workbook.

Dim BreakCell As Range
Dim RowCnt As Integer
Dim LastCol As Long
Dim FirstCol As Long
Dim X As Long

ActiveWindow.View = xlPageBreakPreview

'Set row and column numbers
With ActiveSheet.UsedRange
    RowCnt = .Find(What:="Qty", LookIn:=xlValues).Row
    FirstCol = .Column
    LastCol = .Column + .Columns.Count - 1
End With

   

For X = FirstCol To LastCol
    
    If Cells(RowCnt, X).Text = "Qty" Then Set BreakCell = Cells(RowCnt, X)
    If Cells(RowCnt, X).EntireColumn.PageBreak = xlAutomatic Then
    'soft page break found, add page break before the most recent break cell
        ActiveSheet.VPageBreaks.Add Before:=BreakCell
    'I would guess there's a better way to do this but changing the view is the
    'only way I could get the soft breaks to reset
        ActiveWindow.View = xlNormalView
        ActiveWindow.View = xlPageBreakPreview
    End If
Next

I removed the page breaks reset but you could put it back in there, just keep in mind that it also resets the print size ratio to 100%. This may or may not work as the hard breaks are set based on the soft breaks so you may want to adjust the print size prior to running.

This is good, but I had to modify the following code to get it to work with Excel 2010. I will continue to wok on modifying the code in order to account for the desired number of 3-column groupings. Thanks to the assistance I have been getting from you and jolivanes, I am almost there!

Change:

ActiveSheet.VPageBreaks.Add Before:=BreakCell

To:

ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=BreakCell