Page Breaks when value changes

[redjay]

Hi all

I want some code which will automatically insert a page break every time the data changes in a column. In column B- the data is structured as below;

DOG
<blank cell>
<blank cell>
DOG
<blank cell>
DOG
<blank cell>
<blank cell>
CAT
<blank cell>
CAT
<blank cell>
MOUSE
<blank cell>
MOUSE
MOUSE
<blank cell>

Etc

The code I want would insert the page break every time every time the data changed- but not where there is a blank cell. I hope this makes sense. I realise the blank cells complicate things- the blank cells separating the data cells are there because there is additional;data in columns C,D,E etc

Many thanks for any help anyone can give me with this problem
J

[Wizz]

redjay,
use the following line to assign the pagebreak were you need it.

Please Login or Register  to view this content.

Not quite sure I understand what do you need.
How do you know were to place the Pagebreaks esactly?

[redjay]

Hi wizz

I need the pagebreaks to be inserted automatically wherever there is a change in the data in column B. So in the example, wherever the data changed from DOG to CAT, thats where I need the page break to go....

J

[Wizz]

So, in the following case:
________________________________
DOG
<blank cell>
DOG
<blank cell>
<blank cell>
CAT
<blank cell>
CAT
________________________________

You would need the pagebreak under the last blank cell over CAT or it should be under the las DOG cell (before the blank cell)?

[redjay]

Under the last blank cell before CAT would be perfect....

[Wizz]

Then this should do the trick

Please Login or Register  to view this content.

[Wizz]

Almost forgot; to make the procedure run on every data change in column B, copy the following procedure inside the code page where you have the data:

Please Login or Register  to view this content.

Remember to keep the SetHPageBreaks inside a Module.

[romperstomper]

You could also do this using the Subtotals feature incidentally. Might take a little bit of clean up afterwards if you didn't want the summary rows, but it's quick and easy otherwise!
FWIW.

[redjay]

Hi Wizz

Im a bit of a newb so might need some more help here.

when I try to run SetHPageBreaks, I get a 'Subscript out of range' error?

Thanks for your help so far

J

[Wizz]

Hi redJay;
Wich line is highlighted when the error pops?

[redjay]

Wizz,

Its ok, have solved that problem

One further thing though, can I amend the code so that instead of inserting the page break directly above the change in data- it instead inserts it on the row before that (i.e one row prior to the change in data) ?

Thanks!

[Wizz]

for sure; if you dont care if the previous row is blank or nonBlank, then use the following:

Please Login or Register  to view this content.

Otherwise, we'll need some more modification. Just let me know.

[redjay]

Absolutely brilliant!

Works perfectly. thanks a million

[Wizz]

Nice to hear that; it was a pleasure!

Please remember to tag the thread as [SOLVED].

G.bye

[redjay]

Thanks again Wizz

Can I just ask- is it possible to modify the code so it also puts in a pagebreak immediately after the last data row in column F?

The trouble is that the worksheets im using this on vary in size so I cannot use the same row number each time.

Hope u can help one last time!

J

[Wizz]

Not a Problem.

Just add the following line just after the end of the loop (Outside the For loop).

Please Login or Register  to view this content.