Inserting Pagebreaks every 40 lines...

  • Thread starter Thread starter Trevor
  • Start date Start date
T

Trevor

Hello all,

I'm looking to find a way to simply insert pagebreaks
every 40th row. Is there a way to do this
programatically? Any assistance would be much
appreciated. Thanks.
 
Hi Trevor:
Not elegant but it works:

Sub Macro1()
' Macro recorded 19/01/2004 by Bernard Liengme

Range("A41").Select
For j = 1 To 100
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
ActiveCell.Offset(rowOffset:=40, columnOffset:=0).Activate
Next j
End Sub

You really need to locate where the last used row is, bit this will get you
going.
 
Trevor,

Try something like the following:

Dim RowNdx As Long
Dim LastRow As Long
Dim WS As Worksheet
Set WS = ActiveSheet
LastRow = WS.Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = 41 To LastRow Step 40
WS.Cells(RowNdx, "A").PageBreak = xlPageBreakManual
Next RowNdx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top