VBA to hide current sheet, then create new sheet from template

[MeaclH]

Hi all,
Been trying to figure this out on my own without much luck. I have a workbook that by the month's end should have a workbook for every calendar day.
The workbook currently has a master sheet, with the blank template for each new sheet; and the sheet for the first day of the month.

Once all work has been completed in the first sheet, I'd like to run a macro that hides the completed sheet, then creates an empty sheet for the next calendar day based off the master sheet. I'd like the tab names to change and the date in the top right in cell I1 to change also. Ideally, I'd like the MASTER TEMPLATE sheet to be hidden always.

I can't seem to attach a link, as its from our organsation, not sure if you will be able to access a link from outside it. So I have added an image instead.

Let me know if you have any questions.

Thanks heaps

 

[Micron]

IMO you need a helper cell or something else that flags a sheet as complete. When the code tests for that condition it can hide the sheet, otherwise how would it know that it is complete?

by the month's end should have a workbook for every calendar day.

I think you mean a worksheet for every day?

I'd like the tab names to change

To what?

and the date in the top right in cell I1 to change also.

to what?
This would copy a sheet named "Master" to the end of the sheet stack, but it will append a number at the end of the name unless you tell it what name to give it.
sheets("Master").Copy after:=Sheets(Sheets.Count)
However, Master would need to be made visible in order to copy, but could immediately be hidden again.

 

[MeaclH]

IMO you need a helper cell or something else that flags a sheet as complete. When the code tests for that condition it can hide the sheet, otherwise how would it know that it is complete?

I think you mean a worksheet for every day?

To what?

to what?
This would copy a sheet named "Master" to the end of the sheet stack, but it will append a number at the end of the name unless you tell it what name to give it.
sheets("Master").Copy after:=Sheets(Sheets.Count)
However, Master would need to be made visible in order to copy, but could immediately be hidden again.

Hi Micron, thanks for your reply. Sorry for the hastily worded email.
Ideally, I would set it up as a button, that the user would click on to run the code. The new sheet name would be previous sheet's date + 1 day, as would the date in Cell I1.

Any ideas on a script that would solve this?

Thanks again mate.

Hayden

 

[Micron]

Could probably cobble something together, but you have not explained how code would differentiate between a "completed" sheet and one that is not. Maybe you think that just clicking the button would guarantee that it won't happen until the sheet is completed, but that's not the case. However, if you want to go that route that's fine.