These two Macros work for me - Change accordinging the Column Letter to
Consider, currently (below) set to E ... Also change the First Data row,
currently (below) set to 5

Sub insert_pagebreak()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row
For row_index = lastrow - 1 To 5 Step -1 '5 is the top data row
If Cells(row_index, "E").Value < _
Cells(row_index + 1, "E").Value Then
ActiveSheet.HPageBreaks.Add Befo= _
Cells(row_index + 1, "E")
End If
Next
End Sub

Sub remove_pagebreak()
ActiveSheet.ResetAllPageBreaks
End Sub

Hope this helps,,,

Jim


"Marty" wrote:

I have a very large file of employee data. Each row equals a different
employee. The headings (top row) include Dept#, Employee Name, etc. The file
is sorted by Dept#. Sample:

Dept # Employee Name
123 John Doe
123 Mickey Mouse
124 Minnie Mouse

I need to automatically insert a PageBreak so that every time there is a
change in Dept # the dept starts on a new page. What is the easiest way to do
this that can be repeated everytime I prepare a new report?

Thanks so very much for your help.