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.


Download Practice Workbook


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.

Excel VBA Insert Page Break Based on Cell Value 1

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.

Excel VBA Insert Page Break Based on Cell Value 2


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.

Excel VBA Insert Page Break Based on Cell Value 3

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

Excel VBA Insert Page Break Based on Cell Value

  • 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

Excel VBA Insert Page Break Based on Cell Value

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.

Excel VBA Insert Page Break Based on Cell Value 6

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

Excel VBA Insert Page Break Based on Cell Value 7

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

Excel VBA Insert Page Break Based on Cell Value 8

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.

Excel VBA Insert Page Break Based on Cell Value 9

Steps:

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

Excel VBA Insert Page Break Based on Cell Value 10

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 3page breaks” inside the dataset.

Excel VBA Insert Page Break Based on Cell Value 11

Read More: How to Use Page Break in Excel (7 Suitable Examples)


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:

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

Excel VBA Insert Page Break Based on Cell Value 12

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 3page breaks” inside the dataset.

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

Sample Dataset 2

Steps:

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

Sample VBA Code

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 3page breaks” inside the dataset of the two Sheets.
  • This is the first Sheet called “mSheet1”.

Sample Dataset 4

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

Sample Dataset 5

Read More: How to Insert Multiple Page Breaks in Excel (2 Ways)


Practice Section

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

Practice Dataset


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!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I am passionate about all things related to data and MS Excel is my favorite application. I want to make people's life easier by writing easy-to-follow and in-depth guides here at Exceldemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo