Copy existing worksheet to new worksheet for each day of the month

[mlc37444]

Hi all,

I have an existing template in a worksheet, and I would like to copy that worksheet to a new worksheet for each day of the current month. Each sheet name can be some form of the date, ie "Jul-01, Jul-02, Jul-03" and so on. I've tried a couple different macros but none worked quite right. Could anyone help?

Thank you!
Maggie

 

[John_w]

Try this macro. The template worksheet is named "Template" in the code - change this as required.

Public Sub Add_Daily_Sheets()

    Dim d As Date
    
    Application.ScreenUpdating = False
    With ThisWorkbook.Worksheets("Template")  '<----- Name of 'template' worksheet
        For d = DateSerial(Year(Date), Month(Date), Day(Date)) To DateSerial(Year(Date), Month(Date) + 1, 0)
            .Copy After:=Worksheets(Worksheets.Count)
            ActiveSheet.Name = Format(d, "Mmm-dd")
        Next
        .Activate
    End With
    Application.ScreenUpdating = True
    
End Sub

 

[mlc37444]

This worked! Amazing, thank you. Is there any possible way to exclude weekend dates?

 

[mlc37444]

Actually, sorry - this created the tabs, but it did not copy my template sheet. It is just a 31 empty sheets for July.

 

[John_w]

Is there any possible way to exclude weekend dates?

Add this If ... End If clause:

            If Weekday(d) <> vbSaturday And Weekday(d) <> vbSunday Then
                .Copy After:=Worksheets(Worksheets.Count)
                ActiveSheet.Name = Format(d, "Mmm-dd")
            End If

this created the tabs, but it did not copy my template sheet. It is just a 31 empty sheets for July.

Strange. It should copy everything in the "Template" sheet to the new sheets. If the "Template" sheet is empty the new sheets will be empty.