How to insert page breaks when value changes in Excel?
Supposing, I have a range of cells, and now, I want to insert page breaks into the worksheet when values in column A changes as following screenshot shown. Of course, you can insert them one by one, but are there any quick ways to insert the page breaks at once based on the changed values of one column?
The following VBA code can help you to insert page breaks below based on once column data changes, please do as follows:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Insert page breaks when value changes:
Sub insertpagebreaks() 'updateby Extendoffice 20151228 Dim I As Long, J As Long J = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For I = J To 2 Step -1 If Range("A" & I).Value <> Range("A" & I - 1).Value Then ActiveSheet.HPageBreaks.Add Before:=Range("A" & I) End If Next I End Sub
3. Then press F5 key to run this code, all page breaks have been inserted into the sheet when data changes in column A. See screenshot:
Note: If you want to insert page breaks when value changes base on other column, you should change the A to any other column header in above code.
Sometimes, you may need to insert the page breaks every n rows, here is a useful tool-Kutools for Excel, with its Insert Page Break Every Row utility, you can quickly finish this job in Excel.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.|
After installing Kutools for Excel, please do as follows:( Free Download Kutools for Excel Now! )
1. Click Enterprise > Printing > Insert Page Break Every Row, see screenshot:
2. In the popped out Insert Page Break Every Row prompt box, enter the interval number of rows which you want to insert the page break under the Interval of section, see screenshot:
3. Then click Ok button, the page breaks have been inserted into the current worksheet every 5 rows, see screenshot:
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 4 months agoSorry. EDIT: The first ROW is a column header.
To post as a guest, your comment is unpublished.· 4 months agoHello, Thank you! The thought of VBA makes me sweat. I do have one problem with the results of this code though. The first column is a column header. The code treats this as a change. My first page only shows the column header, but he following pages are fine. FYI: This is for a monthly report and the number of rows for each criteria change monthly. Any Ideas? Thank you in advance.