Gord:
Thank you. However, I am a novice with using a macro. I followed the EXCEL
help directions to get me to a point where I can enter the code you suggest.
I get to the macro recording box. But I can't enter the code there. Where
do I enter the code that you suggest below?

"Gord Dibben" wrote:

First sort by Street Name then by Number.

Then run this macro to insert a pagebreak at each change in Street Name.

Sub Insert_PBreak()
Dim OldVal As String
Dim rng As Range
OldVal = Range("D2")
For Each rng In Range("D2:D7000")
If rng.text < OldVal Then
rng.PageBreak = xlPageBreakManual
OldVal = rng.text
End If
Next rng
End Sub


Gord Dibben MS Excel MVP

On Tue, 12 Feb 2008 10:31:01 -0800, Robert Judge
wrote:

I have an EXCEL 2003 file containing about 7000 addresses like this:
FIRST LAST NUMBER STREET
Mary Smith 123 Main
Tom Jones 789 Maple St.
Fred Clark 456 Main St.

I want to insert page breaks so that when I print it, each street will be
together on the same page. That is, Smith and Clark will be on the same page
because they both live on Main St. and Jones will be another page because he
lives on Maple St.

I would sort the worksheet by Street, then by number. There are more than
one hundred street names. I would rather not have to manually insert each
page break at a new street name. Is there a way to get EXCEL to
automatically make page breaks each time the street name changes? I will
appreciate advice.