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.

 

ArrowRawData

 

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.

ArrowOutput

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube