Automatically adding Page Breaks after nth row

I am running windows 10 and Excel 2016

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 their 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

This can do if rows for any given date is not more then mystep

Please Login or Register  to view this content.

Kind regards
Leo

Leo, thank you for responding to my request for help.

I have copied the code you have provided into my workbook. In particular, the worksheet project I require the automatic page breaks. i.e. a worksheet is called 'May 2016'. Unfortunately, when the code is run I get the following message; 'subscript out of range'.

Does your code need to be inserted into 'This workbook' project or can it be added to ANY worksheet I require?

I thank you for your patience and understanding.

Just change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Kind regards
Leo

Leo, this seems to work now. Thank you very much

Can I expect the code provided to run each time the worksheet e.g. May 2016 is opened and updated with new rows (I may increase the rows for any given date) and automatically adjust the page breaks?

Can you also explain what you mean by when you say '...This can do if rows for any given date is not more then mystep...'

Thanks again.

Say for ex. you have 17 x "02/05/2016" and mystep = 15
then that date will be on 2 pages.

and to run code second time of more, just after

Please Login or Register  to view this content.

this 2

Please Login or Register  to view this content.

Kind regards
Leo

Leo, thank you for the explanation. The code you have provided is working very well. Thank you very much for your expertise and help.
I have an additional question to ask if you don't mind;

1) When does the code run. For example:
a) when the workbook is opened? or
b) when the worksheet is opened? or
c) Do I have to run the code manually?

At present it appears I have to run the code manually. Is this correct?

Thank you again.

Indeed, @ this moment manually
same code in can be in WorkSheet_Activate to or WorkBook_Open

Kind regards
Leo