Formatting‎ > ‎

Add PageBreaks

"How can I automatically set a page break in by database each time there is a change in value in a specific column?"

  1. Dataset must be sorted so like values are together in the key column
  2. Key column must be easily editable in the macro (line to edit is colored in the macro below)


Option Explicit

Sub SetPageBreaks()
'Author:   Jerry Beaucaire,
'Date:     3/2/2010
'Inserts a pagebreak each time the values change in a specific column
'Good for pagebreaking data in a sorted list
Dim LR As Long, StCol As Long, Rw As Long

StCol = 1   'The column to break by comparing value changes   1="A", 2="B", etc...
LR = Cells(Rows.Count, StCol).End(xlUp).Row

    For Rw = LR To 2 Step -1
        If Cells(Rw, StCol) <> Cells(Rw - 1, StCol) Then _
            ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(Rw, StCol)
    Next Rw

End Sub

Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!