How to Insert Page Break Based on Cell Value with Excel VBA

Often, we need to add page breaks in Excel to differentiate values. This is also very helpful for printing purposes. This indicates where one page ends and another page begins. In this article, we will show you 4 Excel VBA codes to insert page break(s) based on cell value.

How to Insert Page Break Based on Cell Value with Excel VBA: 4 Handy Approaches

To demonstrate our methods, we have selected a dataset with 3 columns: “Product”, “Brand”, and “Price”. We will slightly edit this data for each method. We have taken data from a retail electronics store to show price information for 3 types of products.

Before jumping on to the step-by-step guides, you need to make sure that the Developer tab is turned on in the Ribbon. If it is not turned on, then you can turn it on simply by following these steps:

• Firstly, File → Options → Customize Ribbon tab → select Developer.
• Then, press OK.

1. Using Excel VBA to Insert Page Break Based on New Cell Value

In this section, we will insert a page break whenever there is a new cell value on our selected range. This means we will insert a page break after Laptop, Smart Watch, and Fitness Tracker. Now our dataset is in order. If yours is not, then you will need to sort the data to keep similar data together. Here, we will be using the For Next Loop to go through each cell.

Steps:

• First, we bring up the VBA Module window, where we type our codes.
• To do this, from the Developer tab → select Visual Basic.

• So, the VBA window will pop up.
• Next, from the Insert tab, select Module.
• Here, we insert VBA code to insert page breaks.

• After that, type the following code inside the VBA Module window.

Sub Page_Break_New_Cell_Value()
    Dim cRange As Range
    Dim qq As Range
    On Error Resume Next
    Set cRange = Application.InputBox("Specify Condition Column Range", _
    "ExcelDemy", Type:=8)
    If cRange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
    For Each qq In cRange
        ActiveSheet.Rows(qq.Row).PageBreak = xlPageBreakNone
        If qq.Value <> qq.Offset(-1, 0).Value Then
            ActiveSheet.Rows(qq.Row).PageBreak = xlPageBreakManual
        End If
    Next qq
Application.ScreenUpdating = True
End Sub

• Afterward, Save the Module.
• Then, put the cursor inside the Sub procedure and press Run.

• So, our code will execute and it will ask for the cell range to insert the page break based on cell value.
• Then, select the cell range B6:B10. Here, we have omitted cell B5, because we did not want a page break after the text Product.
• Lastly, press OK.

• By doing so, we can see there are 2 page breaks inside the dataset and we have 3 pages.

Read More: How to Insert Page Break in Excel Between Rows

2. Inserting Page Break After Specific Text

For the second method, we will use the VBA IF Then statement inside a For Next Loop to insert a page break after a specific cell value in Excel. We have modified our original dataset a bit by adding 3 Total rows. Moreover, these 3 are merged cells.

Steps:

• Firstly, as shown in the first method, bring up the Module window.
• Secondly, type the following code inside that.

Sub Page_Break_Specific_Text()

Dim qq As Integer
Dim sLastRow As Integer

sLastRow = Cells(Rows.Count, 2).End(xlUp).Row
ActiveSheet.ResetAllPageBreaks

For qq = 5 To sLastRow
    If Cells(qq, 2).Value = "Total" Then
        ActiveSheet.HPageBreaks.Add Before:=Rows(qq + 1)
    End If
Next

End Sub

• Then, as shown in method 1, we Save and Run this Module.
• Thus, this code will insert 3 “page breaks” inside the dataset.

Read More: How to Insert a Page Break in Excel

3. Applying VBA Find Function to Insert Page Break on Cell Value

In this method, we will be using the VBA Find function to look for the cell value “Total” and then we will add a page break where it finds it.

Steps:

• Firstly, as shown in the first method, bring up the Module window.
• Secondly, type the following code inside that.

Sub Add_Page_Break_Condition_VBA_Find()
    Dim cRange As Range, cfAddress As String

    With ActiveSheet.Range("B4:B" & _
    ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row)

        Set cRange = .Cells.Find(What:="Total", LookIn:=xlFormulas, _
                               LookAt:=xlPart, SearchOrder:=xlByRows, _
                               SearchDirection:=xlNext)

        If Not cRange Is Nothing Then
            cfAddress = cRange.Address
            Do
                If Not cRange Is Nothing Then
                    cRange.Offset(1).PageBreak = xlPageBreakManual
                End If

                Set cRange = .FindNext(cRange)

                If cRange Is Nothing Then
                    Exit Do
                End If

                If cRange.Address = cfAddress Then
                    Exit Do
                End If

            Loop
        End If
    End With

End Sub

• Then, as shown in method 1, we Save and Run this Module.
• Thus, this code will insert 3 “page breaks” inside the dataset.

Read More: How to Insert Page Break Between Rows 39 and 40 in Excel

4. Adding Page Break on Cell Value to Multiple Sheets

For the last method, we will be adding page breaks for multiple Sheets based on cell values using Excel VBA. We will add page breaks to two Sheets named: “mSheet1”, and “mSheet2”. Moreover, we will put these two Sheets inside an array and use the VBA UBound and LBound functions to cycle through it. Without further ado, let us get started.

Steps:

• Firstly, as shown in the first method, bring up the Module window.
• Secondly, type the following code inside that.

Sub Page_Break_Condition_Multi_Sheets()

    Dim cMultiSheets, qq As Long, zz As Range
    Application.ScreenUpdating = False
    cMultiSheets = Array("mSheet1", "mSheet2")
    
    For qq = LBound(cMultiSheets) To UBound(cMultiSheets)
        With Worksheets(cMultiSheets(qq))
            .Cells.PageBreak = xlPageBreakNone
            .Columns("B").Insert
            .Columns("C").Copy .Columns("B")
            .Columns("B").Replace "Total", "#NULL!"
            On Error Resume Next
            For Each zz In .Columns("B").SpecialCells(2, xlErrors)
                zz.Offset(1, 0).PageBreak = xlPageBreakManual
            Next zz
            .Columns("B").Delete
        End With
        On Error GoTo 0
    Next qq
    Application.ScreenUpdating = True
End Sub

• Then, as shown in method 1, we Save and Run this Module.
• Thus, this code will insert 3 “page breaks” inside the dataset of the two Sheets.
• This is the first Sheet called “mSheet1”.

• Lastly, this is the second Sheet called “mSheet2” and we can see 3 “page breaks” after the cell value “Total”.

Read More: How to Insert Multiple Page Breaks in Excel

Practice Section

We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.

Download Practice Workbook

Conclusion

We have shown you 4 handy approaches to using Excel VBA to insert page break(s) based on cell value. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Thanks for reading, keep excelling!

Related Articles

• How to Use Page Break in Excel
• How to Print Borders at Page Break in Excel

<< Go Back to Page Break | Page Setup | Print in Excel | Learn Excel