Is there a way to get VBA code to understand where page breaks are ?

[tonywatsonhelp]

Hi everyone,

I currently have a product list that I want to automate using vba the creation of a price list that I can either send or print as a pdf document.

currently because the style of the product list is different to how I want the price list to be I have a macro that cuts and pastes value the detail from the product list to the price list,
to be honest, even I was surprised at how well this has worked, the frustrating thing is that I get page breaks in the middle of products

So for example I might have 20 rows of camera tripods, but the page break is needed so it put it in and I get half at the bottom of page one and the rest at the top of page two,
ideally I'd like to stop this if possible?

the problems are I can't just tell it where to put the page breaks because I might need to add more stock, so this time I might have 5 cameras, 10 tripods and 6 lights on a page but if I add 2 new cameras and 3 new tripods, the lights are moved down the page ?

I know this might be a bit difficult to explain but I'm having no luck finding an answer so I thought Id ask?

I do have one important fact that might make this possible? the subcatagories (the products I want to keep together and not have a page break through are all in a different column to the products!

so for example everything starts at A30,
So A 30 is "Cameras"
A 31 and all of A is blank as the products are stored in Column B so B31 is first camera, and the next cell in column is the next category? for example "lighting"
So if there was a way to set up the page breaks from the macro, maybe what I need is a macro that says, set page break page 1 A1 to A29, page 2 A30 to the nearest cell with data in it that would be close to the bottom of page two (or the top of page 3) if you see what I mean?

i'll try do a little diagram

	A	B	c	D	E	F	G
29	So this should always be the bottom of page 1
30	Cameras
31		this is a list of cameras
32		one more
33	batteries
34		AA bats
35	lights			where as the page break should go here to Lights starts the new page?
36		this is lights
37		and lights	now the problem is if excel wants to put a page break here I cant stop it! so lights get broken up!	see above
38		and lights
39		and lights these could be any amount but not more then 20 so always within one page size, but could be just one
40	tripods
41		tripod1
42		tripod2
43		tripod3
44
45	biscuts
46		Could be any stock
47

<TBODY>
</TBODY>



<TBODY>
</TBODY>

48

























































Now I'd be happy to put one blank row between each subcategory if that makes it easier?

so to summarise, I need VBA code that can set page breaks to only happen above a Cell in ColumnA that has data in it, but not everytime there is a cell with data in it, only when it is where a pagebreak should be?

if someone could help with this it would be amazing, thanks

Tony