Option Explicit

Sub CreateSheets()
Dim strDate As String
Dim NumDays As Long
Dim i As Long
Dim sh As Object
Dim wsBase As Worksheet
On Error GoTo EndIt
    
' The Do statement to captures Month/Year via Input Box
' and return number of days in the month to the NumDays variable
    Do
        strDate = Application.InputBox( _
            Prompt:="Please enter month and year: mm/yyyy", _
            Title:="Month and Year", _
            Default:=Format(Date, "mm/yyyy"), _
            Type:=2)
 
        If strDate = "False" Then Exit Sub
        If IsDate(strDate) Then Exit Do
        If MsgBox("Please enter a valid date, such as ""01/2010""." _
            & vbLf & vbLf & "Shall we try again?", vbYesNo + vbExclamation, _
            "Invalid Date") = vbNo Then End
    Loop
    
    Application.ScreenUpdating = False
    NumDays = Day(DateSerial(Year(strDate), Month(strDate) + 1, 0))
    Set wsBase = Sheets("Template")
    
' For each day, the For statement below copies the template sheet 'n' times
    For i = 1 To NumDays
        wsBase.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = Format(DateSerial(Year(strDate), Month(strDate), i), "ddd mmm dd")
    Next i
EndIt:
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Open()
Dim Today As Date
Dim sh As Worksheet
Dim MyVal As Variant
Dim strDate As Variant
Dim i As Long
Dim NumDays As Variant

  MyVal = Format(DateSerial(Year(strDate), Month(strDate), i), "ddd mmm dd")

   
    For Each sh In ThisWorkbook.Worksheets
        For i = 1 To NumDays
            If sh.Name = MyVal(Today) Then
                sh.Select
            End If
        Next
    Next
    
End Sub