# Macro to change month and year per page

#### girapas

##### Board Regular
I need a sheet to enter data monthly, one month per printing page.
In the first row of each page I want to be the year (column A) and the month (column B).
How can run a macro to prepare a 10 years sheet, starting from January 2011 and ending December 2020 ?

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Fowmy

##### Well-known Member
Could you give a clearer description of how you need the sheets?

#### Comfy

##### Well-known Member
Try This:

Code:
``````Sub Create_Sheets()

Dim Startdate As Date, Enddate As Date, myYear As String, myMonth As String
Dim i As Long, j As Long
Dim ws As Worksheet

Startdate = Application.InputBox("Enter you start Date (dd/mm/yyyy)", "Start Date", Type:=2)
Enddate = Application.InputBox("Enter you start Date (dd/mm/yyyy)", "Start Date", Type:=2)

j = MonthCount(Startdate, Enddate)

myYear = Year(Startdate)
myMonth = Month(Startdate)

For i = 1 To j + 1
If CInt(myMonth) > 12 Then
myMonth = 1
myYear = myYear + 1
End If
ws.Name = MonthName(myMonth, False) + " " + myYear
ws.Cells(1, 1).Value = myYear
ws.Cells(1, 2).Value = MonthName(myMonth, False)
myMonth = myMonth + 1
Next

End Sub
Function MonthCount(pDate1 As Date, pDate2 As Date) As Long

MonthCount = DateDiff("m", pDate1, pDate2)

End Function``````

/Comfy

#### girapas

##### Board Regular
Sorry, I wasn't so clear about what I want.
I need only 1 sheet, that I'll print in A4 paper. The months will change in each page of the sheet.
An example:
(page 1)
A B C D ........
1 2011 January
2 (data) (data) (data)
3 (data) (data) (data)
4 ... ...
.
.
.

(page 2)
A B C D .........
1 2011 February
2 (data) (data) (data)
3 (data) (data) (data)
4 ... ... ...
.
.
.

Replies
9
Views
129
Replies
2
Views
324
Replies
2
Views
111
Replies
2
Views
405
Replies
4
Views
261

1,186,692
Messages
5,959,211
Members
438,404
Latest member
RailEngineer76

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back