Excel has a wide range of usage. In this article, we will show you how to create a calendar in a worksheet by using a macro.
Except for storing and analyzing data in your Excel, you can also use it to finish other tasks. For example, you can collect information by using Excel, or you can note down your planning in the worksheet. Today we have found a new usage. You are able to create a calendar in a worksheet. You can see the effect in the image below.
You can input some tasks into this calendar. And the function is the same as a memorandum. By using the Excel, those tasks will be clearer. Now follow the steps in the following part and see how it works.
Create a Calendar
- Press the button “Alt +F11” on the keyboard to open the worksheet.
- And then insert a new module in the Visual Basic editor.
- Now copying the following codes into the new module:
Sub Create_Monthly_Calender() Dim firstweekday As Integer, EndDay As Integer, _ FirstWeekColumnIndex As Integer, AssignmentDate As Integer, _ FirstCountNumber As Integer, SecondCountNumber As Integer, _ LastDay As Range, objRange As Range, RowIndexofLastday As Integer, FirstCountforTargetRange As Integer, SecondCountforTargetRange As Integer firstday = InputBox("Input the year, month and the first day with this format: year/month/day") If firstday = "" Then Exit Sub Range("A1:G1").Merge Range("A1") = Year(firstday) & "." & Month(firstday) Range("A2") = "Sunday" Range("A2").AutoFill Destination:=Range("A2:G2"), Type:=xlFillDefault firstweekday = Application.WorksheetFunction.Weekday(firstday) Cells(3, firstweekday) = 1 Select Case Month(firstday) Case 1, 3, 5, 7, 8, 10, 12 EndDay = 31 Case 4, 6, 9, 11 EndDay = 30 Case 2 If (Year(firstday) Mod 4) = 0 And (Year(firstday) Mod 100) <> 0 Or ((Year(firstday) Mod 400) = 0) Then EndDay = 29 Else EndDay = 28 End If End Select For FirstWeekColumnIndex = 1 To (7 - firstweekday) Cells(3, firstweekday).Offset(0, FirstWeekColumnIndex) = Cells(3, firstweekday).Offset(0, FirstWeekColumnIndex - 1) + 1 Next FirstWeekColumnIndex AssignmentDate = Range("G3") + 1 For FirstCountNumber = 2 To 10 Step 2 For SecondCountNumber = 0 To 6 Cells(3, firstweekday).Offset(FirstCountNumber, 1 - firstweekday + SecondCountNumber) = AssignmentDate AssignmentDate = AssignmentDate + 1 If Cells(3, firstweekday).Offset(FirstCountNumber, 1 - firstweekday + SecondCountNumber) = EndDay Then Exit For End If Next SecondCountNumber If Cells(3, firstweekday).Offset(FirstCountNumber, 1 - firstweekday + SecondCountNumber) = EndDay Then Exit For End If Next FirstCountNumber ’set format for the range With Range("A1") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Font.Size = 16 .Font.Bold = True .Interior.Color = RGB(196, 202, 201) End With With Range("A2:G2") .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Font.Bold = True End With For Each LastDay In ActiveSheet.UsedRange If LastDay = EndDay Then RowIndexofLastday = LastDay.Row End If Next For FirstCountforTargetRange = RowIndexofLastday To 3 Step -2 With Range("A" & FirstCountforTargetRange, "G" & FirstCountforTargetRange) .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .RowHeight = 20 End With Next FirstCountforTargetRange For SecondCountforTargetRange = RowIndexofLastday + 1 To 4 Step -2 With Range("A" & SecondCountforTargetRange, "G" & SecondCountforTargetRange) .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Font.Bold = True .RowHeight = 50 .ColumnWidth = 12 End With Next SecondCountforTargetRange Set objRange = Range("A1", "G" & (RowIndexofLastday + 1)) With objRange.Borders .Color = vbBlack .Weight = xlThin .LineStyle = xlContinuous End With ActiveWindow.DisplayGridlines = False Cells(3, firstweekday).Offset(1, 0).Select End Sub
Thus is a very lone macro. But you can directly use it. And you don’t need to modify it.
- Then press the button “F5” on the keyboard.
- In the pop up window, input the date of the first day with the format “Year/Month/Day”. The “Day” can be any day in this month, here we will input “1”.
- And then click the button “OK”. Next you will come back to the editor. In order to check the result, you can come back to the worksheet.
The calendar has already appeared in the worksheet. And the cursor will be positioned at the blank cell of the first day.
On the other hand, if you are not satisfied with the cells format, you can also modify the codes according to your preference. With this calendar, you are sure to improve your work efficiency.
Excel is not Less Prone to Failure
If you use Excel frequently, it will not be hard for you to find that Excel will always corrupt. Most of the time, exit Excel and then restart it can solve the problem. However, sometimes you will meet with severe Excel corruption. And at this moment, you can use our powerful tool to repair Excel xlsx data error. By using this tool, almost all the errors can be solved easily.
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word docx file and outlook repair software products. For more information visit www.datanumen.com