How do I insert multiple page breaks simultaneously in Excel?

[Guest]

I have a spreadsheet with multiple entries that have to be printed on
separate pages. I thought I could select the rows by clicking on the row and
Ctrl and then click on Insert Page Break - and boom, it will insert all 150
page breaks. Or do I really have to spend a whole hour doing this mindless
task manually? Please help.

 

[Ron de Bruin]

Hi Marina

Maybe this macro will help ti insert a pagebreak every 20 rows (change to your number)

If row 1 is a header row and you want to print it on every page then
change RW + 1 to RW + 2 and use File>Page Setup>Sheet to fill in $1:$1
in the "Rows to repeat at top: " box.

This example will use row 1 till the last row with data in column A .

Sub Insert_PageBreaks()
Dim Lastrow As Long
Dim Row_Index As Long
Dim RW As Long

'How many rows do you want between each page break
RW = 20
With ActiveSheet
'Remove all PageBreaks
.ResetAllPageBreaks
'Search for the last row with data in Column A
Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For Row_Index = RW + 1 To Lastrow Step RW
.HPageBreaks.Add Before:=.Cells(Row_Index, 1)
Next
End With
End Sub

 

[Guest]

Unfortunately, there is NO pattern as to how many rows come after each page
break. Is there still another way?

 

[Dave Peterson]

If there's no pattern, how do you know where to put those pagebreaks when you do
it manually?

Maybe you have a keyword that indicates that a pagebreak goes above/below this
word???

If you have one, I bet Ron could modify that code to help you.

 

[Ron de Bruin]

If you have one, I bet Ron could modify that code to help you.

Sure

 

[Guest]

I just now figured out a different way to get to my goal. But I'd still like
to know how to insert multiple page breaks simultaneously. Yes, there is no
pattern because the spreadsheet has multiple people's names and each person
has a different number of rows of information, so I wanted each person's
information to go onto the next page. So, I was wondering if I could just
click on Ctrl and select the rows where the new person begins and then insert
page breaks for all of them simultaneously.

 

[Gord Dibben]

This macro will place a pagebreak at every change in name in column A.

Maybe that will work?

Sub InsertBreak_At_Change()
Dim i As Long
For i = Selection.Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) <> Selection(i - 1) And Not IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP