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

4 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
```
Justin Abbott Aug 28, 2025 at 1:38 AM

Have you had any issues with your Macros after installing 2025-08 Cumulative Update for Windows 11 Version 24H2 for x64-based Systems (KB5063878) (26100.4946)
run time error 1004

Multiple Macros aer now failing

I even tried:
Method 2 – Applying a VBA Code to Insert Multiple Page Breaks in Excel

Thanks
- Reply
  Shamima Sultana Aug 28, 2025 at 9:44 AM
  
  Hello Justin Abbott,
  
  Thank you for sharing the details. The runtime error 1004 usually occurs if Excel security settings or references are affected after a Windows/Office update. Since you mentioned the issue started after installing the Windows 11 cumulative update, here are a few things you can try:
  
  Check Excel References: In the VBA editor, go to Tools > References and make sure none of them show as Missing.
  
  Re-enable Macros: Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and confirm macros are enabled.
  
  Repair Office: Sometimes updates break VBA links. Run a Quick Repair (or Online Repair) from the Office installer.
  
  Re-run the Code: If you’re using Method 2 VBA, confirm the correct sheet is active when running the macro. Adding Worksheets(“SheetName”).Activate before inserting page breaks can help.
  
  Rollback / Update: If the issue persists, check if Microsoft has released a follow-up fix, or temporarily uninstall the update to see if macros start working again.
  
  This seems to be linked to the recent update, so hopefully Microsoft will patch it soon.
  
  Regards
  ExcelDemy

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