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