Is it possible to get vba to create page breaks at "Good" places on a sheet

[tonywatsonhelp]

Hi Everyone

I have a document that has 4 headers

"Sales Today"
"Sales This Week"
"Sales This Month"
"Sales This year"

Now I have a macro that creates a PDF from this sheet,
whilst i can create the print area i'm struggling to get page breaks in nice places on the PDF

as you can Imagine I have no idea how many rows each section will take up, but its a pain having to move the page breaks when they are in the wrong places. such as right in the middle of a set of data
is there a way to use a macro to do the following?

With active sheet, print area, check to see if there is more than one page, if so do this,

with each page, if any of the above sections is split over two pages add a page break before it?

or something like that so basically it looks to see if the page break is in a silly place and moves it so the pages are breaked at headers (unless the section is bigger than one page,)

I know this is a big ask so any ideas are worth a try.

So you know the Headers are in column J, but it also holds other data, there are no blank rows in the print area column J

please let me know if there's a way, or if you have any clever tricks to help avoid this happening.

Thanks
Tony

 

[Micron]

Off the wall, but use an Access report? You could create a db, import (link) spreadsheet and try making a report from it? Access can output the report as a pdf, and reports have a fair amount of flexibility and options for grouping, sorting, keeping groups with next or previous, etc., not to mention events that you can code for. You can even show (or not) a page break based on a running sum, such as the # of records in a section.

 

[tonywatsonhelp]

Thanks Micron,
I''ll take alok at that
Tony