Hello All

Got a calendar all on one worksheet. It puts each date in like this:
CELL A1: =DATE(year, 1,1), A2: =A1+1, A3: =A2+1, etc. I want to print it out with each month on a separate piece of paper. I can't put the page breaks in manually because in leap years I'll end up with 29 Feb on the top of the March page, 31 Mar on the top of the April page, etc, etc. How can I get the page breaks to go in so that they'll always separate the months right for every year?

Hi,

Am Sat, 29 Dec 2012 12:23:09 -0800 (PST) schrieb robzrob:

Got a calendar all on one worksheet. It puts each date in like this:
CELL A1: =DATE(year, 1,1), A2: =A1+1, A3: =A2+1, etc. I want to print it out with each month on a separate piece of paper. I can't put the page breaks in manually because in leap years I'll end up with 29 Feb on the top of the March page, 31 Mar on the top of the April page, etc, etc. How can I get the page breaks to go in so that they'll always separate the months right for every year?

try:

Sub PageBreaks()
Dim i As Integer
Dim LRow As Integer

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To LRow
If Month(.Cells(i, 1)) Month(.Cells(i - 1, 1)) Then
.HPageBreaks.Add .Cells(i, 1)
End If
Next
End With
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Saturday, 29 December 2012 20:36:54 UTC, Claus Busch wrote:
Hi,



Am Sat, 29 Dec 2012 12:23:09 -0800 (PST) schrieb robzrob:



Got a calendar all on one worksheet. It puts each date in like this:

CELL A1: =DATE(year, 1,1), A2: =A1+1, A3: =A2+1, etc. I want to print it out with each month on a separate piece of paper. I can't put the page breaks in manually because in leap years I'll end up with 29 Feb on the top of the March page, 31 Mar on the top of the April page, etc, etc. How can I get the page breaks to go in so that they'll always separate the months right for every year?



try:



Sub PageBreaks()

Dim i As Integer

Dim LRow As Integer



With ActiveSheet

LRow = .Cells(.Rows.Count, 1).End(xlUp).Row

For i = 2 To LRow

If Month(.Cells(i, 1)) Month(.Cells(i - 1, 1)) Then

.HPageBreaks.Add .Cells(i, 1)

End If

Next

End With

End Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2



Tried it with 2013 and 2016. Works fine. Thank you.