Renaming multiple tabs to read day and month.

[newtoexcelvab]

I have a spreadsheet that has a tab for everyday of the month> I would like for each tab to read day of week, month of year and the date of that particular day
for example
sheet(1)= Tue Jan 01
sheet(2)=Wed Jan 02
sheet(3)=Thu Jan 03
and so on until
sheet(31)= Thu Jan 31
I am new to excel and to vba, so all information as to where at in the coding I need insert provided coding is very much appreciated?
thanks in advance for any and all help that you forum readers can give.

 

[Biz]

Create worksheet tab called "template".

Vba below copies it for each day of month.

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 yy")
    Next i
EndIt:
    Application.ScreenUpdating = True
End Sub

 

[Biz]

Sorry an error in previous post

Try code below

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

 

[newtoexcelvab]

I have ran the above macro and am having trouble with it. The input box comes up and asks for the year but then after that nothing happens. what could I possibly be doing wrong?

 

[Biz]

I have ran the above macro and am having trouble with it. The input box comes up and asks for the year but then after that nothing happens. what could I possibly be doing wrong?

Make sure you have worksheet tab called "Template".

Biz

 

[newtoexcelvab]

Thanks so much. This works perfectly.

 

[Biz]

Thanks so much. This works perfectly.

No worries m8.

 

[Manageri21]

Hi man, i find useful your code, i want to ask how to make for all year?

Sorry an error in previous post

Try code below

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

 

[Wizerd]

I also used the code above - saved me a ton of time. I only use it for the date (1-31) and would like to loop it twice to create (tabs like '01A, 01B, 02A, 02B..ect), or Days and Aft added for two shift reporting. Any ideas would be helpful.