Macro to Copy master sheet and name it next day (Day2, day3 etc)

[tonywatsonhelp]

Hi Everyone,
Hope someone can help me with this one,
I have a sheet that has an input page and a totals page,
What I'd like to do is create copies of the Input page for each day and as we go forward so we can keep the data separate,
so I need a macro that can copy a master sheet rename it and date it for me.

So here's what I'm looking for,
a macro that runs when i click it and does this,
Copies "Master" sheet and Places it at the end,
Names It Day (and the next number) so if this has been done before last day might be "Day21" so it would be "Day22" (the Document all starts out with just the master sheet and "Day1") if its easier the number can be in cell C1 so you name it "Day&C1"
Ask me in a message box "what Date this day if for?" and insert the answer into B1 of that sheet, this is the actual date formatted as DD/MM/yyyy.

so I end up with a new sheet, call "Day(something)" and all the days are in order, and each sheet has a date in B1

Please help if you can

Thanks

Tony

 

[mumps]

Assuming that the Master sheet starts out with the value of 1 in cell C1, try:

Sub CreateSheet()
    Application.ScreenUpdating = False
    Dim response As String
    response = Format(InputBox("What Date this day if for?"), "dd/mm/yyyy")
    If response = "" Then Exit Sub
    Sheets("Master").Copy after:=Sheets(Sheets.Count)
    With ActiveSheet
        .Name = "Day" & Sheets("Master").Range("C1").Value + 1
        .Range("B1") = response
        .Range("C1").ClearContents
    End With
    Sheets("Master").Range("C1") = Sheets("Master").Range("C1") + 1
    Application.ScreenUpdating = True
End Sub

 

[tonywatsonhelp]

Thank you Mumps
This looks great
Tony

 

[mumps]

You are very welcome.