Create Page Breaks in Excel

Often you might have a large table of data that needs to be grouped and printed on separate pages based on the value in a column. This procedure asks for the column containing the group by value, clears any previous page breaks, resets the page breaks and goes to print preview mode.

Sub PageBreaks()
' Place page breaks on a worksheet based on the value in
' the column specified by the user. Sets the first row
' as headers to repeat on each page and activates the
' print preview mode.

Dim strColumn As String ' Column containing the break value
Dim lRows As Long ' Rows in the used range
Dim lTitleRow As Long ' Row containing the column headers
Dim vaValue1 As Variant ' 1st value to compare
Dim vaValue2 As Variant ' 2nd value to compare
Dim l As Long ' loop counter

lRows = ActiveSheet.UsedRange.Rows.Count

strColumn = InputBox("Enter the column containing the page break information.")
' Exit the sub if the user presses cancel.
If strColumn = "" Then
Exit Sub
End If

ActiveSheet.ResetAllPageBreaks
lTitleRow = 1

For l = 2 To lRows
Cells(l + 1, strColumn).Activate
vaValue1 = Cells(l, strColumn).Value
vaValue2 = Cells(l + 1, strColumn).Value
If vaValue2 <> vaValue1 Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
End If
Next l

ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
ActiveSheet.PrintPreview

Range("A1").Activate

End Sub