Gord:
Please ignore my post from earlier today. Futher research showed me that I
would have to use the VBE. I did so, creating a macro the same as you
suggested. The only difference is I changed "D" to "J" because the street
name is in Column J, not D. My macro is below. However, it does not work.
When I run it, the screen flickers for a few seconds, as if the macro is
running. When the flicker stops, I look at the worksheet in print preview,
and I see that there are no page breaks. Is there something wrong in my
macro?

Sub Insert_PBrak()
Dim OldVal As String
Dim rng As Range
OldVal = Range("J2")
For Each rng In Range("J2:J11000")
If rng.Text < OldVal Then
rng.PageBreak = xlPageBreakManual
OldVal = rng.Text
End If
Next rng
End Sub
----------------------------------------------------
"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.