Forum Discussion

Jack_Ingber's avatar
Jack_Ingber
Copper Contributor
Jan 12, 2023

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

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 ...
excel
Formulas and Functions
Macros and VBA
JKPieterse's avatar
JKPieterse
Silver Contributor
Jan 12, 2023

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
    Next
End Sub