Create sheet for every day of month using Master template, name each sheet as per day of month. VBA

[MeaclH]

Gday everyone,

I have found a few VBA codes that do the trick but I have a slightly different need.
I would like to have a workbook for every month that we have operations. Each workbook needs to contain a sheet for each day of the month. Some of the codes that I have found prompt an input box for month and year etc which would be nice.
I would like to run a VBA code that creates a sheet for each day of the month using the Master sheet as a template, and have each sheet named as per the day of month. Like I say I have found some codes to do the above but what I also need is to figure out how to put the corresponding date on each sheet a certain cell (H1). So it's easily identifiable to our team what date they are working on, rather than the sheet tabs at the bottom.

Any ideas how to tie it all together, when creating all the sheets from the master? Also, we are down under, so any date format would be appreciated in DD MM YYYY

Thanks for your help

Cheers,
Hayden

 

[John_w]

Try this:

Public Sub Create_Daily_Sheets()

    Dim inputMonthYear As String, inputDate As Date
    Dim d As Date
    
    inputMonthYear = InputBox("Enter month and year (MM YYYY)", "Create sheet for every day of month", Format(Date, "MM YYYY"))
    If inputMonthYear = "" Then Exit Sub
        
    inputDate = CDate("01 " & inputMonthYear)
    Application.ScreenUpdating = False
    With ActiveWorkbook.Worksheets("Master")
        For d = DateSerial(Year(inputDate), Month(inputDate), Day(inputDate)) To DateSerial(Year(inputDate), Month(inputDate) + 1, 0)
            .Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
            ActiveSheet.Name = Format(d, "dd mm")
            ActiveSheet.Range("H1").Value = d
            ActiveSheet.Range("H1").NumberFormat = "dd mm yyyy"
        Next
        .Activate
    End With
    Application.ScreenUpdating = True
    
End Sub

 

[MeaclH]

Try this:

Public Sub Create_Daily_Sheets()

    Dim inputMonthYear As String, inputDate As Date
    Dim d As Date
   
    inputMonthYear = InputBox("Enter month and year (MM YYYY)", "Create sheet for every day of month", Format(Date, "MM YYYY"))
    If inputMonthYear = "" Then Exit Sub
       
    inputDate = CDate("01 " & inputMonthYear)
    Application.ScreenUpdating = False
    With ActiveWorkbook.Worksheets("Master")
        For d = DateSerial(Year(inputDate), Month(inputDate), Day(inputDate)) To DateSerial(Year(inputDate), Month(inputDate) + 1, 0)
            .Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
            ActiveSheet.Name = Format(d, "dd mm")
            ActiveSheet.Range("H1").Value = d
            ActiveSheet.Range("H1").NumberFormat = "dd mm yyyy"
        Next
        .Activate
    End With
    Application.ScreenUpdating = True
   
End Sub

John, a big hearty thank you from me. This code works unbelievably well!
Cheers

 

[gabemom1]

John, a big hearty thank you from me. This code works unbelievably well!
Cheers

Hi John,

Following this thread, I am looking to create a daily worksheet for the month copying from a template. How do I expand this code to do that? When I use the code, it just created 1 day for me but not all 30 days for September.

 

[John_w]

The code works for the OP's specific case, so if you can't determine why it isn't working for you I suggest you start a new thread, detailing your precise requirements.