hello
How can I insert a page break in a list of names at each different last
name, so I can print separately for each last name? There are 50 names and
over 5,000 records in the spreadsheet. Is there a way to insert page break
automatically? Any help is appreciated.
thank you

Assuming last names are in column A

Sub Insert_PBreak()
Dim OldVal As String
Dim rng As Range
OldVal = Range("A1")
For Each rng In Range("A1:A5000") '<< change range
If rng.text < OldVal Then
rng.PageBreak = xlPageBreakManual
OldVal = rng.text
End If
Next rng
End Sub


Gord Dibben MS Excel MVP

On Tue, 5 May 2009 12:16:09 -0700, da wrote:

hello
How can I insert a page break in a list of names at each different last
name, so I can print separately for each last name? There are 50 names and
over 5,000 records in the spreadsheet. Is there a way to insert page break
automatically? Any help is appreciated.
thank you

I just created a macro for my spreadsheet using your example below. In my
spreadsheet, I need to print a separate page per claim number. I sorted my
spreadsheet by claim number, then ran my macro. It worked like a charm!
Thanks!

"Gord Dibben" wrote:

Assuming last names are in column A

Sub Insert_PBreak()
Dim OldVal As String
Dim rng As Range
OldVal = Range("A1")
For Each rng In Range("A1:A5000") '<< change range
If rng.text < OldVal Then
rng.PageBreak = xlPageBreakManual
OldVal = rng.text
End If
Next rng
End Sub


Gord Dibben MS Excel MVP

On Tue, 5 May 2009 12:16:09 -0700, da wrote:

hello
How can I insert a page break in a list of names at each different last
name, so I can print separately for each last name? There are 50 names and
over 5,000 records in the spreadsheet. Is there a way to insert page break
automatically? Any help is appreciated.
thank you

Good to hear.

Thanks for feeding back.


Gord

On Wed, 27 May 2009 20:11:01 -0700, sjg366
wrote:

I just created a macro for my spreadsheet using your example below. In my
spreadsheet, I need to print a separate page per claim number. I sorted my
spreadsheet by claim number, then ran my macro. It worked like a charm!
Thanks!

"Gord Dibben" wrote:

Assuming last names are in column A

Sub Insert_PBreak()
Dim OldVal As String
Dim rng As Range
OldVal = Range("A1")
For Each rng In Range("A1:A5000") '<< change range
If rng.text < OldVal Then
rng.PageBreak = xlPageBreakManual
OldVal = rng.text
End If
Next rng
End Sub


Gord Dibben MS Excel MVP

On Tue, 5 May 2009 12:16:09 -0700, da wrote:

hello
How can I insert a page break in a list of names at each different last
name, so I can print separately for each last name? There are 50 names and
over 5,000 records in the spreadsheet. Is there a way to insert page break
automatically? Any help is appreciated.
thank you