Workbooks copy help

So I have a tricky problem I believe. I am looking to make a copy of a workbook that is a weekly log of activities for a systems group. At the end of the week I would like to have the users click a button and have a new copy of the workbook created omitting any information that was entered for the week. In addition I would like it to save the new workbook with the next weeks date on the end of the name of the file.

To boot all the sheets are password protected, the workbook is restricted for editing and then the workbook is shared.

Any thoughts?

Create a template file (meaning no data, just formats and formulas and protection). Then add this code to a module:

Sub CreateNewFromTemplateFile()

    Dim TemplateFilePath As String
    Dim TemplateFileName As String
    Dim DestinationFilePath As String
    Dim DestinationFileName As String
    
    On Error GoTo Err_Handler
    
    TemplateFilePath = "C:\Your Template Folder\"
    TemplateFileName = "Template File.xlsm"
    DestinationFilePath = "C:\Your Archive Folder\"
    DestinationFileName = "Archive File"
    
    Workbooks.Open TemplateFilePath & TemplateFileName
    
    Workbooks(TemplateFileName).SaveAs DestinationFilePath & DestinationFileName & Format(DateAdd("d", 8 - Application.Weekday(Date, 2), Date), "yyyy-mm-dd"), xlOpenXMLWorkbookMacroEnabled
    
Exit Sub
Err_Handler:
    MsgBox ("Error number " & Err.Number & ": " & Err.Description)
End Sub

Then tie this macro to a button or shortkey or something and have your users run it at the appropriate time.

So this seems to be working somewhat. It doesn't seem to save the new workbook in the directory I am specifying and thus isn't giving the new file the date on the end. Any thoughts? When I click the button it is just bringing up the template file only in a new workbook but not renaming or saving.

Check that. Seems to be working once I had the proper security settings to write to the destination drive.

Make sure you have the trailing backslash in the TemplateFilePath. Also, try adding the .xlsm to the DestinationFileName

Here is another question based on the same file. If I wanted to save the file as instructed with the date at the end but also change the names of the sheets within the workbook to reflect the days of the upcoming week is that possible? I am thinking no or at least not without great difficulty.

Thanks!

There are probably better ways to do this, but here is what I came up with in a couple of minutes:

Sub CreateNewFromTemplateFile()

    Dim TemplateFilePath As String
    Dim TemplateFileName As String
    Dim DestinationFilePath As String
    Dim DestinationFileName As String
    Dim Sht As Worksheet
    Dim ShtCount As Integer
    
    On Error GoTo Err_Handler
    
    TemplateFilePath = "C:\Your Template Folder\"
    TemplateFileName = "Template File.xlsm"
    DestinationFilePath = "C:\Your Archive Folder\"
    DestinationFileName = "Archive File"
    
    Workbooks.Open TemplateFilePath & TemplateFileName
    
    For Each Sht In Workbooks(TemplateFileName)
        Sht.Name = Format(DateAdd("d", 8 - Application.Weekday(Date, 2) + ShtCount, Date), "yyyy-mm-dd")
        ShtCount = ShtCount + 1
    Next Sht
    
    Workbooks(TemplateFileName).SaveAs DestinationFilePath & DestinationFileName & Format(DateAdd("d", 8 - Application.Weekday(Date, 2), Date), "yyyy-mm-dd"), xlOpenXMLWorkbookMacroEnabled
    
Exit Sub
Err_Handler:
    MsgBox ("Error number " & Err.Number & ": " & Err.Description)
End Sub