How do I insert page breaks in a huge EXCEL file?

[Robert Judge]

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.

 

[Gord Dibben]

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("D27000")
If rng.text <> OldVal Then
rng.PageBreak = xlPageBreakManual
OldVal = rng.text
End If
Next rng
End Sub


Gord Dibben MS Excel MVP

 

[Robert Judge]

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?

 

[Robert Judge]

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]

Is column J the street name column?

Has it been sorted ascending?

Are all the Mains, Maples and other street names grouped together?

If so, your code should work.

My advice about sorting by number after sorting by street I think is not needed
or desirable.

Just select all columns and sort by street name only.


Gord.