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.

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.

Yes, it did the trick which is a BIG help! Thank you so very much!

I'm curious about the part regarding the first data row. In my worksheet,
the first row to contain data is 2. The macro seems to works the same
regardless of whether the code reads

For row_index = lastrow - 1 To 5 Step -1 '5 is the top data row"
or
"For row_index = lastrow - 1 To 2 Step -1 '2 is the top data row"

Am I missing something in my test that might show up later? Sorry to be so
inexperienced and such a pain.

Thank you so much for sharing.


"Jim May" wrote:

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.

You could step-through the code using the F8 key - Tile both the Code window
and the actual spreadsheet so that you can see both as you F8 through the
code.

Nothing beats a visual look-see at how the cursor moves with each line of
code.
I do it all the time...
Jim

"Marty" wrote:

Yes, it did the trick which is a BIG help! Thank you so very much!

I'm curious about the part regarding the first data row. In my worksheet,
the first row to contain data is 2. The macro seems to works the same
regardless of whether the code reads

For row_index = lastrow - 1 To 5 Step -1 '5 is the top data row"
or
"For row_index = lastrow - 1 To 2 Step -1 '2 is the top data row"

Am I missing something in my test that might show up later? Sorry to be so
inexperienced and such a pain.

Thank you so much for sharing.


"Jim May" wrote:

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.