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 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.

 

Jack

excel
Formulas and Functions
Macros and VBA

1 Reply

  • 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