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

We have a dataset with 3 columns: Product, Brand, and Price. We will slightly edit this data for each method.

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:

Go to File, select Options, then go to the Customize Ribbon tab and select Developer.
Press OK.

Method 1 – Using Excel VBA to Insert a Page Break Based on New Cell Value

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. Our dataset is sorted. If yours is not, then you will need to sort the data to keep similar data together.

Steps:

From the Developer tab, select Visual Basic.

The VBA window will pop up.
From the Insert tab, select Module.

Use 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

We are calling our Sub procedure Page_Break_New_Cell_Value.
We declare the variable types.
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.
We use a For Next Loop to go through each cell of our selected range.
If the value of a cell is different than the previous cell, we add a page break to it.
This code inserts page breaks whenever a new cell value appears.

Save the Module.
Press Run.

Excel will ask for the cell range to insert the page break based on cell value.
Select the cell range B6:B10. We have omitted cell B5 because we did not want a page break after the text Product.
Press OK.

There are 2 page breaks inside the dataset and we have 3 pages.

Method 2 – Inserting a Page Break After Specific Text

We have modified our original dataset a bit by adding 3 Total rows. Moreover, these are merged cells.

Steps:

As shown in the first method, bring up the Module window.
Use the following code inside.

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

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

Save and Runthis Module.
This code will insert 3 page breaks inside the dataset.

Method 3 – Applying the VBA Find Function to Insert a Page Break on Cell Value

We will be using the VBA Find function to look for the cell value “Total” and add a page break where it finds it.

Steps:

As shown in the first method, bring up the Module window.
Use the following code inside.

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

We are calling our Sub procedure Add_Page_Break_Condition_VBA_Find.
We declare the variable types.
We find the last row of our dataset.
The code looks for the text “Total” in the defined range.
The starting value is 4, as our data starts from there.
We use a Do Loop to go through each cell of our selected range.
If the value of a cell is “Total”, it adds a page break before that cell.
This code inserts page breaks whenever that specific cell value appears.

Save and Run this Module.
This code will insert 3 page breaks inside the dataset.

Method 4 – Adding a Page Break on Cell Value to Multiple Sheets

We will add page breaks to two Sheets named “mSheet1” and “mSheet2”. We will put these two Sheets inside an array and use the VBA UBound and LBound functions to cycle through it.

Steps:

As shown in the first method, bring up the Module window.
Insert the following code.

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

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