Macro to change month and year per page

girapas

Board Regular
Joined
Apr 20, 2004
Messages
150
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 ?
Thanks in advance
 

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
Joined
Jul 10, 2008
Messages
512
Office Version
  1. 365
Platform
  1. Windows
Could you give a clearer description of how you need the sheets?
 
Upvote 0

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,386
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
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
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
 
Upvote 0

girapas

Board Regular
Joined
Apr 20, 2004
Messages
150
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 ... ... ...
.
.
.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top