Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 24
Default How do I insert page breaks in a huge EXCEL file?

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default How do I insert page breaks in a huge EXCEL file?

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.


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 24
Default How do I insert page breaks in a huge EXCEL file?

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.



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 24
Default How do I insert page breaks in a huge EXCEL file?

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.



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default How do I insert page breaks in a huge EXCEL file?

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.

On Mon, 18 Feb 2008 06:58:00 -0800, Robert Judge
wrote:

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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I insert multiple page breaks simultaneously in Excel? marina Excel Discussion (Misc queries) 7 April 4th 23 10:48 AM
insert an excel sheet into a word doc and keep excel page breaks. Diane Excel Discussion (Misc queries) 0 September 10th 07 12:34 AM
How do I insert page breaks in .txt for use in excel? JohnK Excel Discussion (Misc queries) 2 January 20th 07 12:24 AM
Insert Multiple Page Breaks heater Excel Worksheet Functions 1 September 13th 06 10:35 PM
Auto insert page breaks in Excel, based on a sort of numbers peterc89 Excel Discussion (Misc queries) 0 November 9th 05 04:41 PM


All times are GMT +1. The time now is 06:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"