Add page breaks to sorted data using VBA in Microsoft Excel

In this article, we will create a macro to insert page breaks, based on a certain condition.

Raw data consists agent wise data, which includes Agent name, Client name, Phone number and Email id.

We want data of a particular agent on separate pages while printing. So, we want to insert a page break at the end of each agent's data.

Logic explanation

We have created “InsertingPagebreak” macro to insert a page break. We make comparison of values of 2 consecutive cells in the first column to find the last cell related to a particular agent and insert a page break.

Code explanation

ActiveSheet.ResetAllPageBreaks

The above code is used to remove any previous page breaks inserted in the sheet.

If Cells(LngRow, LngCol).Value <> Cells(LngRow - 1, LngCol).Value Then

The above code is used to compare consecutive cells within the first column.

ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(LngRow, LngCol)

The above code is used to insert page breaks before specified cells.

Please follow below for the code

Sub InsertingPagebreak()

'Declaring variables
Dim LngCol As Long
Dim LngRow, MaxRow As Long

'Clear existing page breaks
ActiveSheet.ResetAllPageBreaks

LngCol = 1

'Getting row number of last cell
MaxRow = Range("A11").SpecialCells(xlCellTypeLastCell).Row

'Looping through all the rows starting from thirteenth row
For LngRow = 13 To MaxRow

    'Comparing values in cell of two consecutive rows for the specified column
    If Cells(LngRow, LngCol).Value <> Cells(LngRow - 1, LngCol).Value Then
        
        'Inserting page break
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(LngRow, LngCol)
                                      
    End If
    
Next LngRow

End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com