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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,865
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,628
Assuming that the Master sheet starts out with the value of 1 in cell C1, try:
VBA Code:
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
 

Forum statistics

Threads
1,144,388
Messages
5,724,071
Members
422,533
Latest member
garganod1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top