Tom’s Tutorials For Excel: Set Page Breaks For Specified Areas

Tom’s Tutorials For Excel: Set Page Breaks For Specified Areas

If your worksheet has areas of data that you want to print on separate pages, you can easily establish page breaks based on a wide choice of cell properties or text values. With the following macro for the pictured example, page breaks are set below each cell in column A that starts with “Total”.

Sub PageBreakInsert()
Cells.PageBreak = xlPageBreakNone
Dim cell As Range
For Each cell In Columns(1).SpecialCells(xlCellTypeConstants)
If Left(cell.Value, 5) = "Total" Then
With ActiveSheet
.HPageBreaks.Add Cells(cell.Row + 1, 1)
.DisplayAutomaticPageBreaks = True
End With
End If
Next cell
End Sub