Page break when data changes....

T

tfarley

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?!

:)
 
D

Dave Peterson

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.
 
T

tfarley

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.
 
R

ryguy7272

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 Before:= _
Cells(row_index + 1, "B")
End If
Next

End Sub


Regards,
Ryan---
 
T

tfarley

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!!
 
D

Dave Peterson

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.
 
T

tfarley

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 said:
Read this:
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm#Creating_Your_First_Macro

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

Regards,
Ryan---
Click to expand...
 
Top