How to Insert Multiple Page Breaks in Excel - 2 Methods

This is the sample dataset.

Method 1 – Utilizing the Page Layout tab to Insert Multiple Page Breaks in Excel

Step 1:

Choose the entire row to the right of the place you want to insert the page break. Here, multiple rows are selected one by one and a one-page break is inserted after each row.
Select Page Layout and choose Breaks.
Click Insert Page Break.

Step 2:

10 page breaks are inserted in the X-axis.

Step 3:

5-page breaks are inserted in the Y-axis.

Method 2 – Applying a VBA Code to Insert Multiple Page Breaks in Excel

Step 1:

Go to the Developer tab.
Select Visual Basic.

Step 2:

Select Insert and choose Module.

Step 3:

Copy the following VBA code into the Module.
To run the code, click “Run” or press F5.

Sub Insert_Multiple_PageBreaks()
'Declaring variable for lastrow
Dim Lastrow As Long
'Declaring Mysheet as worksheet
Dim Mysheet As Worksheet
'Setting Mysheet for Activesheet
Set Mysheet = Application.ActiveSheet
'Type row number for how much row gap you need to insert page breaks
Row = Application.InputBox("Enter Row Number", TitleId, "", Type:=1)
'Reset all page breaks
Mysheet.ResetAllPageBreaks
'Page breaks starts from B1 cell till last row with respect to X axis
Lastrow = Mysheet.Range("B1").SpecialCells(xlCellTypeLastCell).Row
For i = Row + 1 To Lastrow Step Row
Mysheet.HPageBreaks.Add Before:=Mysheet.Cells(i, 1)
Next
End Sub

VBA Code Breakdown

calls the Sub Procedure Insert_Multiple_PageBreaks.
refers to the current Worksheet as ActiveSheet.
defines the row number using Row = Application.InputBox(“Enter Row Number”, TitleId, “”, Type:=1).
Page breaks will start from B1 cell till the last row in the X-axis using Lastrow = Mysheet.Range(“B1”).SpecialCells(xlCellTypeLastCell).Row and For i = Row + 1 To Lastrow Step Row

Step 4:

Enter the row number. Here, 1 for the entire row.
Click OK.

Step 5:

15-page breaks are inserted in the X-axis.

Download Practice Workbook

Download the following Excel workbook.

Insert Multiple Page Breaks.xlsm

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

Get FREE Advanced Excel Exercises with Solutions!

Sub SpecificAmountOfPageBreaksWithStartingLine() Dim Lastrow As Long Dim Mysheet As Worksheet Set Mysheet = Application.ActiveSheet Row = Application.InputBox("Enter Amount of Row Number", TitleId, "", Type:=1) NumBreaks = Application.InputBox("Enter The Number of Page Breaks", TitleId, "", Type:=1) StartPoint = Application.InputBox("Enter The Starting Line Number", TitleId, "", Type:=1) Mysheet.ResetAllPageBreaks Lastrow = Mysheet.Range("B1").SpecialCells(xlCellTypeLastCell).Row For i = StartPoint To Lastrow Step Row If i <= StartPoint + (Row * (NumBreaks - 1)) Then Mysheet.HPageBreaks.Add Before:=Mysheet.Cells(i, 1) End If Next End Sub

2 Comments

Eric Schoenthaler Feb 4, 2023 at 4:38 AM

this is fantastic and obviously way above my pay grade. Kudos!
This is close to what I want to accomplish and seems to be the only website on the internet with information close to what I would like to accomplish.

I do have a question how to modify your code. What if I want to insert 25 page breaks at line 7? Or 9 page breaks starting at line 125. How do I modify the code to insert a specific amount of page breaks starting at a specific line?
- Reply
  Lutfor Rahman Shimanto Feb 5, 2023 at 3:55 PM
  Hello ERIC SCHOENTHALER,
  Thank you for your nice words! Great that you found our website and information helpful and relevant to your goal. I’m glad we could provide you with the information you were looking for. Regarding your question, you can use the following VBA code to insert a specific amount of page breaks starting at a particular line.
  Regards
  Lutfor Rahman Shimanto
```
Sub SpecificAmountOfPageBreaksWithStartingLine()
Dim Lastrow As Long
Dim Mysheet As Worksheet
Set Mysheet = Application.ActiveSheet
Row = Application.InputBox("Enter Amount of Row Number", TitleId, "", Type:=1)
NumBreaks = Application.InputBox("Enter The Number of Page Breaks", TitleId, "", Type:=1)
StartPoint = Application.InputBox("Enter The Starting Line Number", TitleId, "", Type:=1)
Mysheet.ResetAllPageBreaks
Lastrow = Mysheet.Range("B1").SpecialCells(xlCellTypeLastCell).Row
For i = StartPoint To Lastrow Step Row
If i <= StartPoint + (Row * (NumBreaks - 1)) Then
Mysheet.HPageBreaks.Add Before:=Mysheet.Cells(i, 1)
End If
Next
End Sub
```

How to Insert Multiple Page Breaks in Excel – 2 Methods

Method 1 – Utilizing the Page Layout tab to Insert Multiple Page Breaks in Excel

Method 2 – Applying a VBA Code to Insert Multiple Page Breaks in Excel

Related Articles

Leave a reply Cancel reply