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

To divide a table into two sections for printing or to divide a table into two, we may need to insert a page break. If not, Excel will print the worksheet using its built-in page breaks, paper size, etc. Depending on our preferences, we can manually insert page breaks to print a worksheet. In this article, we will show you how to Insert Multiple Page Breaks in Excel.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


2 Handy Approaches to Insert Multiple Page Breaks in Excel

When printing a worksheet, page breaks are used to divide it into distinct pages. Excel will utilize default page breaks that are set to the size of a printed page if you don’t set them, and this nearly never looks good. In the following two methods, you will learn a very convenient method to insert multiple page breaks in Excel by applying VBA Code and utilizing the Page Layout tab manually as well. Let’s suppose we have a sample data set.

Sample Data


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

In this first method, you will learn how to insert multiple page breaks in Excel by utilizing the Page Layout tab.

Step 1:

  • Just to the right of where you want the page break to occur, choose one entire row. Here, we will select multiple rows one by one after using a one-page break every time.
  • Firstly, select the Page Layout tab.
  • Secondly, choose the Breaks tool.
  • Thirdly, click on the Insert Page Break command.

Sample Data

Step 2:

  • The consequences of 10 repeated page breaks will finally be inserted with respect to the X-axis, as you can see.

Sample Data

Step 3:

  • As you can see, the effects of 5-page breaks will be added with respect to the Y-axis also.

Sample Data

Read More: How to Insert Page Break in Excel Between Rows


2. Applying VBA Code to Insert Multiple Page Breaks in Excel

In this last section, we will generate a VBA code utilizing the Developer tab to insert multiple page breaks in Excel.

Step 1:

  • At first, we will use the Developer tab.
  • Then, we will select the Visual Basic command.

Handy Approaches to Insert Multiple Page Breaks in Excel

Step 2:

  • Here, the Visual Basic window will open.
  • After that, from the Insert option, we will choose the new Module to write a VBA Code.

Handy Approaches to Insert Multiple Page Breaks in Excel

Step 3:

  • Now, paste the following VBA code into the Module
  • To run the program, click the “Run” button 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

Handy Approaches to Insert Multiple Page Breaks in Excel

VBA Code Breakdown

  • Firstly, we call our Sub Procedure Insert_Multiple_PageBreaks.
  • Then, we refer to our current Worksheet as ActiveSheet.
  • Besides, we enter our row number using Row = Application.InputBox(“Enter Row Number”, TitleId, “”, Type:=1).
  • Finally, Page breaks will start from the B1 cell till the last row with respect to X-axis using Lastrow = Mysheet.Range(“B1”).SpecialCells(xlCellTypeLastCell).Row and For i = Row + 1 To Lastrow Step Row

Step 4:

  • Now, enter the row number here. We will choose 1 for the entire row.
  • Then, click OK.

Handy Approaches to Insert Multiple Page Breaks in Excel

Step 5:

  • Finally, you will observe that the results of 15-page breaks for the entire will be inserted with respect to the X-axis.

Handy Approaches to Insert Multiple Page Breaks in Excel

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


Conclusion

In this article, I’ve covered 2 handy methods to Insert Multiple Page Breaks in Excel. I sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles

Bishawajit Chakraborty

Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo