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

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

Download Practice Workbook

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

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

2. Inserting Page Break After Specific Text

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

4. Adding Page Break on Cell Value to Multiple Sheets

Practice Section

Conclusion

Download Practice Workbook

VBA Page Break.xlsm

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

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

VBA Code Breakdown

First, we are calling our Sub procedure Page_Break_New_Cell_Value.
Then, we declare the variable types.
Afterward, we are defining the source cell range as an InputBox. Here, type 8 denotes the input should be Range type only. Moreover, if we select nothing, then the code will stop.
Then, we use a For Next Loop to go through each cell of our selected range.
After that, if the value of a cell is different than the previous cell, we add a page break to it.
Thus, this code works to insert page breaks whenever a new cell value appears.

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 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.

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

VBA Code Breakdown

First, we are calling our Sub procedure Page_Break_Specific_Text.
Then, we declare the variable types.
Next, we find the last row of our dataset.
Then, the code resets the page breaks.
Afterward, the starting value is 5, as our data starts from there. We could have also started from 4.
Then, we use a For Next Loop to go through each cell of our selected range.
After that, if the value of a cell is “Total”, it adds a page break before that cell.
Thus, this code works to insert page breaks whenever that specific cell value appears.

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

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

VBA Code Breakdown

First, we are calling our Sub procedure Add_Page_Break_Condition_VBA_Find.
Then, we declare the variable types.
Next, we find the last row of our dataset.
Then, the code looks for the text “Total” in the defined range.
Afterward, the starting value is 4, as our data starts from there.
Then, we use a Do Loop to go through each cell of our selected range.
After that, if the value of a cell is “Total”, it adds a page break before that cell.
Thus, this code works to insert page breaks whenever that specific cell value appears.

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

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

VBA Code Breakdown

First, we are calling our Sub procedure Page_Break_Condition_Multi_Sheets.
Then, we declare the variable types.
Next, we use the VBA LBound and UBound functions to cycle through the two Sheets.
After that, it clears all page breaks.
Then, it inserts a column, copies values from column B, and replaces “Total” with an error “#NULL!”.
Then, the code looks for the text “Total” in the defined range.
After that, if the value of a cell is “Total”, it adds a page break before that cell.
Afterward, it deletes column B.
Thus, this code works to insert page breaks whenever that specific cell value appears.

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”.

Practice Section

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

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. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!