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.
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.
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
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 firstname.lastname@example.org
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.