View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Ron de Bruin Ron de Bruin is offline
external usenet poster
 
Posts: 11,123
Default How do I insert multiple page breaks simultaneously in Excel?

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 FilePage SetupSheet 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 Befo=.Cells(Row_Index, 1)
Next
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Marina" wrote in message ...
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.