Page break when data changes....
 

I need to know if it's possible to have a macro (or something automated) to
insert a page break at every change of data in my column A of data. (I've
got about 15,000 rows of data and will probably end up with 9,000 page
breaks.)

I'd like to end up with something like this:

A B C
SORT ACCT SHARES
Apple, Anne 12345 50,000
Apple, Anne 20987 7,000
--------- page break ---------------
Banana, Mike 25678 14,000
Banana, Mike 59873 600
--------- page break --------------
Banana, Karen 25679 34,000

I'm ultimately trying to get the data for each SORT person to print on a
letter template.

Can ya help me out?!

:-)

Page break when data changes....
 

You could use
Data|subtotals
and choose to insert a page break between groups.

And you'll be able to subtotal the shares, too.

And you'll be able to use the outlining symbols to the left to hide/show
details.

tfarley wrote:

I need to know if it's possible to have a macro (or something automated) to
insert a page break at every change of data in my column A of data. (I've
got about 15,000 rows of data and will probably end up with 9,000 page
breaks.)

I'd like to end up with something like this:

A B C
SORT ACCT SHARES
Apple, Anne 12345 50,000
Apple, Anne 20987 7,000
--------- page break ---------------
Banana, Mike 25678 14,000
Banana, Mike 59873 600
--------- page break --------------
Banana, Karen 25679 34,000

I'm ultimately trying to get the data for each SORT person to print on a
letter template.

Can ya help me out?!

:-)

--

Dave Peterson

Page break when data changes....
 

Thank you for your reply! I did try that and the reason it didn't work is
because apparently there is a limit on the number of page breaks that a
subtotal can enter into a worksheet and I exceed that number.

"Dave Peterson" wrote:

You could use
Data|subtotals
and choose to insert a page break between groups.

And you'll be able to subtotal the shares, too.

And you'll be able to use the outlining symbols to the left to hide/show
details.

tfarley wrote:

I need to know if it's possible to have a macro (or something automated) to
insert a page break at every change of data in my column A of data. (I've
got about 15,000 rows of data and will probably end up with 9,000 page
breaks.)

I'd like to end up with something like this:

A B C
SORT ACCT SHARES
Apple, Anne 12345 50,000
Apple, Anne 20987 7,000
--------- page break ---------------
Banana, Mike 25678 14,000
Banana, Mike 59873 600
--------- page break --------------
Banana, Karen 25679 34,000

I'm ultimately trying to get the data for each SORT person to print on a
letter template.

Can ya help me out?!

:-)

--

Dave Peterson

Page break when data changes....
 

With fruits in Column A, and Names in Column B, etc.:

Sub Insert_PBreak()
Dim OldVal As String
Dim rng As Range

lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, "B").Value < _
Cells(row_index + 1, "B").Value Then
ActiveSheet.HPageBreaks.Add Befo= _
Cells(row_index + 1, "B")
End If
Next

End Sub


Regards,
Ryan---

--
RyGuy


"tfarley" wrote:

Thank you for your reply! I did try that and the reason it didn't work is
because apparently there is a limit on the number of page breaks that a
subtotal can enter into a worksheet and I exceed that number.

"Dave Peterson" wrote:

You could use
Data|subtotals
and choose to insert a page break between groups.

And you'll be able to subtotal the shares, too.

And you'll be able to use the outlining symbols to the left to hide/show
details.

tfarley wrote:

I need to know if it's possible to have a macro (or something automated) to
insert a page break at every change of data in my column A of data. (I've
got about 15,000 rows of data and will probably end up with 9,000 page
breaks.)

I'd like to end up with something like this:

A B C
SORT ACCT SHARES
Apple, Anne 12345 50,000
Apple, Anne 20987 7,000
--------- page break ---------------
Banana, Mike 25678 14,000
Banana, Mike 59873 600
--------- page break --------------
Banana, Karen 25679 34,000

I'm ultimately trying to get the data for each SORT person to print on a
letter template.

Can ya help me out?!

:-)

--

Dave Peterson

Page break when data changes....
 

Okay.... I don't understand what you just wrote. Anyway you can translate
that into leyman terms?!? (I'm sorry....I feel bad, but I'm not a expert
user.....)

Thank you!!

"ryguy7272" wrote:

With fruits in Column A, and Names in Column B, etc.:

Sub Insert_PBreak()
Dim OldVal As String
Dim rng As Range

lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, "B").Value < _
Cells(row_index + 1, "B").Value Then
ActiveSheet.HPageBreaks.Add Befo= _
Cells(row_index + 1, "B")
End If
Next

End Sub


Regards,
Ryan---

--
RyGuy


"tfarley" wrote:

Thank you for your reply! I did try that and the reason it didn't work is
because apparently there is a limit on the number of page breaks that a
subtotal can enter into a worksheet and I exceed that number.

"Dave Peterson" wrote:

You could use
Data|subtotals
and choose to insert a page break between groups.

And you'll be able to subtotal the shares, too.

And you'll be able to use the outlining symbols to the left to hide/show
details.

tfarley wrote:

I need to know if it's possible to have a macro (or something automated) to
insert a page break at every change of data in my column A of data. (I've
got about 15,000 rows of data and will probably end up with 9,000 page
breaks.)

I'd like to end up with something like this:

A B C
SORT ACCT SHARES
Apple, Anne 12345 50,000
Apple, Anne 20987 7,000
--------- page break ---------------
Banana, Mike 25678 14,000
Banana, Mike 59873 600
--------- page break --------------
Banana, Karen 25679 34,000

I'm ultimately trying to get the data for each SORT person to print on a
letter template.

Can ya help me out?!

:-)

--

Dave Peterson

Page break when data changes....
 

You're right. I think that the number of page breaks is a little over a 1000.
(I think!)

I think you're going to have to rethink your problem.



tfarley wrote:

Thank you for your reply! I did try that and the reason it didn't work is
because apparently there is a limit on the number of page breaks that a
subtotal can enter into a worksheet and I exceed that number.

"Dave Peterson" wrote:

You could use
Data|subtotals
and choose to insert a page break between groups.

And you'll be able to subtotal the shares, too.

And you'll be able to use the outlining symbols to the left to hide/show
details.

tfarley wrote:

I need to know if it's possible to have a macro (or something automated) to
insert a page break at every change of data in my column A of data. (I've
got about 15,000 rows of data and will probably end up with 9,000 page
breaks.)

I'd like to end up with something like this:

A B C
SORT ACCT SHARES
Apple, Anne 12345 50,000
Apple, Anne 20987 7,000
--------- page break ---------------
Banana, Mike 25678 14,000
Banana, Mike 59873 600
--------- page break --------------
Banana, Karen 25679 34,000

I'm ultimately trying to get the data for each SORT person to print on a
letter template.

Can ya help me out?!

:-)

--

Dave Peterson


--

Dave Peterson

Page break when data changes....
 

Read this:
http://www.anthony-vba.kefra.com/vba...ur_First_Macro

Copy and paste the macro that I gave you into a module.
Run the code in the module.

Regards,
Ryan---


--
RyGuy


"Dave Peterson" wrote:

You're right. I think that the number of page breaks is a little over a 1000.
(I think!)

I think you're going to have to rethink your problem.



tfarley wrote:

Thank you for your reply! I did try that and the reason it didn't work is
because apparently there is a limit on the number of page breaks that a
subtotal can enter into a worksheet and I exceed that number.

"Dave Peterson" wrote:

You could use
Data|subtotals
and choose to insert a page break between groups.

And you'll be able to subtotal the shares, too.

And you'll be able to use the outlining symbols to the left to hide/show
details.

tfarley wrote:

I need to know if it's possible to have a macro (or something automated) to
insert a page break at every change of data in my column A of data. (I've
got about 15,000 rows of data and will probably end up with 9,000 page
breaks.)

I'd like to end up with something like this:

A B C
SORT ACCT SHARES
Apple, Anne 12345 50,000
Apple, Anne 20987 7,000
--------- page break ---------------
Banana, Mike 25678 14,000
Banana, Mike 59873 600
--------- page break --------------
Banana, Karen 25679 34,000

I'm ultimately trying to get the data for each SORT person to print on a
letter template.

Can ya help me out?!

:-)

--

Dave Peterson


--

Dave Peterson

Page break when data changes....
 

Thank you!

I did that.....and I get the same type error, saying that I can only have
1026 pagebreaks per worksheet.

So, now I think my next step should just be do split my data into Alpha
files (A, B, C, etc) and run that script for each smaller file and then I
should be golden!

Thank you Thank you Thank you!

"ryguy7272" wrote:

Read this:
http://www.anthony-vba.kefra.com/vba...ur_First_Macro

Copy and paste the macro that I gave you into a module.
Run the code in the module.

Regards,
Ryan---


--
RyGuy


"Dave Peterson" wrote:

You're right. I think that the number of page breaks is a little over a 1000.
(I think!)

I think you're going to have to rethink your problem.



tfarley wrote:

Thank you for your reply! I did try that and the reason it didn't work is
because apparently there is a limit on the number of page breaks that a
subtotal can enter into a worksheet and I exceed that number.

"Dave Peterson" wrote:

You could use
Data|subtotals
and choose to insert a page break between groups.

And you'll be able to subtotal the shares, too.

And you'll be able to use the outlining symbols to the left to hide/show
details.

tfarley wrote:

I need to know if it's possible to have a macro (or something automated) to
insert a page break at every change of data in my column A of data. (I've
got about 15,000 rows of data and will probably end up with 9,000 page
breaks.)

I'd like to end up with something like this:

A B C
SORT ACCT SHARES
Apple, Anne 12345 50,000
Apple, Anne 20987 7,000
--------- page break ---------------
Banana, Mike 25678 14,000
Banana, Mike 59873 600
--------- page break --------------
Banana, Karen 25679 34,000

I'm ultimately trying to get the data for each SORT person to print on a
letter template.

Can ya help me out?!

:-)

--

Dave Peterson


--

Dave Peterson