VBA to copy current sheet (template) and make one sheet for each day of the month

[Jnb99]

Hi,

The title says it all. I want to use a macro to copy the current sheet(template) to a new sheet complete with the values I've put on the sheet, and the name of the sheet to be renamed to the current date. There is previous threads but not what I need.

Thanks!

 

[footoo]

Have a look here :

https://www.pcreview.co.uk/threads/rename-new-worksheet-with-the-current-date.3705219/

 

[steve the fish]

Something like:

For Each sh In ThisWorkbook.Sheets
    If sh.Name = Format(Date, "dd-mm-yy") Then
        Exit Sub
    End If
Next
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Date, "dd-mm-yy")

 

[Jnb99]

Have a look here :

https://www.pcreview.co.uk/threads/rename-new-worksheet-with-the-current-date.3705219/

Thank you, this worked 100%!

Now I want to clear the template sheet when I click on the save button I assigned the macro to. I used:

Sub copysheetandnamedate()myname = Format(Date, "dd-mm-yy")
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = myname
ActiveSheet.Range("C6:G55").ClearContents
End Sub

But the new sheet it creates is cleared and not my template, what am I doing wrong?

 

[Jnb99]

Thanks a lot guys, I got it to work. I used:

Sub copysheet()Dim WS As Worksheet
Set WS1 = Worksheets("StockTakeTemplate")
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = Format(Date, "mm-dd-yyyy")
WS1.Range("C6:G55").ClearContents
End Sub

Does anyone have something better?

 

[steve the fish]

Thats pretty close to what i gave you. However i would use mine not yours and adapt it to suit your sheet name. Yours has issues. It would fail should it be run twice on the same day. It will also not do what you expect should you run it when any sheet other than stocktaketemplate is the active sheet.

 

[Jnb99]

I think I am doing something wrong, because it run an error with your code?

Is there a way to protect the new sheet when you save?

 

[footoo]

I think I am doing something wrong, because it run an error with your code?

Is there a way to protect the new sheet when you save?

What is the error message and what line of code?
Is the sheet you are copying named "Template" ?

 

[Jnb99]

The error is: Compile error, Invalid outside procedure.
The sheet name is "StockTakeTemplate"