tonywatsonhelp
Well-known Member
- Joined
- Feb 24, 2014
- Messages
- 3,194
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
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
<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
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>
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