I am trying to create an excel calendar for my boss. He'd like it to be based off of a daily calendar template in excel and to generate sequential daily tabs for an entire month, with us inputting the date (not using the current date as start date). I have 2 macros that I was able to create that we essentially want to combine in a manner.
This is the macro with the template that I was able to configure for monthly tabs for the entire year.
Sub AddYearly()
Dim wbk As Workbook
Dim wsh As Worksheet
Dim i As Long
Application.ScreenUpdating = False
Set wbk = ActiveWorkbook
Set wsh = wbk.Worksheets("Daily Calendar")
For i = 1 To 12
wsh.Copy After:=wbk.Worksheets(wbk.Worksheets.Count)
wbk.Worksheets(wbk.Worksheets.Count).Name = MonthName(i)
Next i
Application.ScreenUpdating = True
End Sub
Then I have the macro with the sequential daily tabs, but it starts on the current date, i.e. I can't make it for specific months at a time and keep them in individual workbooks (ex: 1 for September, 1 for October, etc).
Sub daily_calendar()
Dim sh As Worksheet, nsh As Worksheet
Dim nextdate As Date
Dim ndate As Date
Dim x As Integer
Dim startdate As Date
x = 1
Worksheets("Sheet1").Range("A1").Select
Worksheets("Sheet1").Range("A1").NumberFormat = ("[$-x-sysdate]dddd, mmmm dd, yyyy")
Worksheets("Sheet1").Range("A1").Value = Date
Cells.EntireColumn.AutoFit
startdate = Worksheets("Sheet1").Range("A1").Value
'Pulls a total of 31 days into the spreadsheet.
For x = 1 To 31
nextdate = startdate + x
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Range("A1").NumberFormat = ("[$-x-sysdate]dddd, mmmm dd, yyyy")
ActiveSheet.Range("A1").Value = nextdate
Cells.EntireColumn.AutoFit
ActiveSheet.Name = Format(nextdate, "[$-x-sysdate]dddd, mmmm dd, yyyy")
Next
End Sub
Thank you in advance!
This is the macro with the template that I was able to configure for monthly tabs for the entire year.
Sub AddYearly()
Dim wbk As Workbook
Dim wsh As Worksheet
Dim i As Long
Application.ScreenUpdating = False
Set wbk = ActiveWorkbook
Set wsh = wbk.Worksheets("Daily Calendar")
For i = 1 To 12
wsh.Copy After:=wbk.Worksheets(wbk.Worksheets.Count)
wbk.Worksheets(wbk.Worksheets.Count).Name = MonthName(i)
Next i
Application.ScreenUpdating = True
End Sub
Then I have the macro with the sequential daily tabs, but it starts on the current date, i.e. I can't make it for specific months at a time and keep them in individual workbooks (ex: 1 for September, 1 for October, etc).
Sub daily_calendar()
Dim sh As Worksheet, nsh As Worksheet
Dim nextdate As Date
Dim ndate As Date
Dim x As Integer
Dim startdate As Date
x = 1
Worksheets("Sheet1").Range("A1").Select
Worksheets("Sheet1").Range("A1").NumberFormat = ("[$-x-sysdate]dddd, mmmm dd, yyyy")
Worksheets("Sheet1").Range("A1").Value = Date
Cells.EntireColumn.AutoFit
startdate = Worksheets("Sheet1").Range("A1").Value
'Pulls a total of 31 days into the spreadsheet.
For x = 1 To 31
nextdate = startdate + x
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Range("A1").NumberFormat = ("[$-x-sysdate]dddd, mmmm dd, yyyy")
ActiveSheet.Range("A1").Value = nextdate
Cells.EntireColumn.AutoFit
ActiveSheet.Name = Format(nextdate, "[$-x-sysdate]dddd, mmmm dd, yyyy")
Next
End Sub
Thank you in advance!