Have Sheet Titles / Cells auto-populate a month's dates

Occasional Visitor

Howdy, very very very basic Excel User here. 

My work uses excel as a scheduling function, and instead of building a month by month schedule where we need to plug in a date format of "5 Mar" as a sheet title, and a cell within "Thursday, 5 March", there has to be an easier way where either on the first sheet we throw in the first date of the month, and it makes 30-31 sheets with those titles, or we have a saved master with 31 blank sheets, and we put it in the first and the rest auto populate. Thanks in advance for the help.



1 Reply

@Jack_Ingber I'm assuming you are using Excel on your desktop, not Excel on-line.

Suppose you have prepaired a blank monthsheet named "BlankMonthSheet", then this macro will ask for a date and then create daily worksheets for the month in the date you provided:


Sub CreateDailySheets()
    Dim inputDate As Date
    Dim dayCt As Long
    Dim ct As Long
    On Error Resume Next
    inputDate = InputBox("Please enter a date in the month for which you want daily sheets created", "Create monthly sheets")
    If Err.Number <> 0 Then
        MsgBox "Please enter a valid date", vbExclamation + vbOKOnly
        Exit Sub
    End If
    'Set to first day of month
    inputDate = DateSerial(Year(inputDate), Month(inputDate), 1)
    '# of days in month
    dayCt = DateSerial(Year(inputDate), Month(inputDate) + 1, 0) - inputDate + 1
    For ct = 1 To dayCt
        With ThisWorkbook.Worksheets
            With .Add(after:=ThisWorkbook.Worksheets(.Count))
                .Name = Format(inputDate + ct - 1, "d mmm")
            End With
        End With
End Sub