Sometimes, an Excel report or worksheet contains a large data set. In that case, it is easier for users or readers to read the report with a page break in it. Certainly, this feature of Excel helps the readers analyze or understand the report without getting bored or monotonous. But, in some cases, users fail to insert a page break in Excel properly. Despite inserting the page break, users can not find it in the worksheet. Therefore, in this article, we will show you the solution to the page break in Excel not working.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
2 Suitable Solutions for Page Break Not Working in Excel
First of all, consider the following data as our sample data set for this article. Here, we have three different data tables, each containing the names, IDs, and joining dates of employees of three different companies for example. Therefore, we will solve the problem given in this article by using this data set. Here, you will see two different solutions regarding the error of page break in Excel not working. We will solve the problem using the Page Setup group in our first method and then, apply Visual Basic for Applications (VBA) in the second one.
1. Using Page Setup Group
We can solve the page break problem in Excel by using the Page Setup group. To do that, go through the following steps.
- Firstly, we will insert a page break in the worksheet.
- For this purpose, we will select row 9.
- Secondly, go to the Page Layout tab of the ribbon.
- Then, in the Page Setup group choose the Insert Page Break command from the Breaks drop-down menu.
- Thirdly, follow the same procedure to insert the page break in row 17.
- Consequently, you will notice that there are no page breaks in those rows.
- Therefore, we will fix the problem in our next steps.
- Firstly, we will go to the Page Layout tab of the ribbon again.
- Then, click on the little arrow on the lower right side of the Page Setup group.
- Secondly, after clicking, you will see a dialogue box named “Page Setup”.
- Then, the Page tab of the dialogue box, choose the option Adjust to if it was not marked previously.
- Thirdly, press OK.
- Finally, you will see the page breaks in the worksheet.
2. Applying VBA Code to Fix Page Break Issue
In our second approach, we will apply VBA to solve the problem. Follow the following steps for a better understanding.
- First of all, take the following data set to insert a page break in rows 10 and 17.
- Meanwhile, you will not notice any page breaks on the worksheet after applying the Insert Page Break command.
- For this reason, we will apply VBA to solve the problem.
- Secondly, go to the Developer tab of the ribbon and choose the Visual Basic command from the Code group.
- Then, after choosing the command, you will see a new tab.
- Further, choose the Module command from the Insert tab.
- Thirdly, insert the following code into the module.
Sub Page_Break_Not_Working() 'Communication with the printer is turned off Application.PrintCommunication = False 'Set the print area to cell B2:D23 on sheet VBA ActiveSheet.PageSetup.PrintArea = "$B$2:$D$23" 'Switch the display in VBA sheet to page break preview ActiveWindow.View = xlPageBreakPreview ActiveWindow.View = xlNormalView End Sub
- We will name the function in the VBA as Page_Break_Not_Working.
- Here, we will declare the communication of the worksheet with printer is turned on or off.
- Then, we will set the worksheet area or cell range within which the page break will be applied by using the command line .ActiveSheet.PageSetup.PrintArea = “$B$2:$D$23”
- Finally we will write the xlPageBreakPreview command to show the page break in the worksheet.
- Therefore, save the code and press the play button or F5 for the solution.
- Finally, you will see page breaks in the worksheet after running the code.
That’s the end of this article. I hope you find this article helpful. After reading this article, you will be able to find the solution to page break in Excel not working by using any of the methods. Please share any further queries or recommendations with us in the comments section below.