I have a workbook that contains around 14 worksheets - static data, Year overview, and 12 monthly worksheets.

The 'year overview' worksheet contains a row for each date of the year. However, each date will contain a minimum of 2 rows per day, but can go up to around 6 rows for any given date. Therefore this worksheet can contain around 1000 rows.

For each of the 12 monthly worksheets, the data for say, May, is copied onto its respective monthly worksheet.

What I am trying to achieve is to automatically implement page breaks on the 'Year Overview' and each of the individual '12 monthly' worksheets - say after row 15(variable if possible). This could be day 7 or day 8 for that month depending on the number of rows added for any given day(s).

I also want to ensure that no date is split across the page break.

Can you please assist in providing automation to automatically add page breaks after nn rows and ensure that no date is split across a page break.

I thank you in anticipation.


Col. A contains the date
Col. B to ?. data from static data worksheet

Well, it seems to me you need to do three things:
1) Determine how to set a page break. I never use them, myself—all the tools I create are for use on the PC—so I guess you'd have to use the macro recorder to figure out how this works.

2) Have your program count lines from the top of the "page". When it's time to set a page break, do so (see above) and start the count over from that point.

3) But as to the decision about where to set the break—which I imagine is the real crux of your question—it depends: Do you want to assume that every group is six lines long, or do you want to go to the extra trouble of counting each group as you encounter it and decide whether each group can fit on the current page? The latter is more complicated but more satisfying to the self-approval :-), a heady but dangerous draught.

Which would you rather do? On your choice depends the logic that must follow.