Workbooks copy help

[cstavr1]

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?

[royUK]

The best way would be to create a template file

[Whizbang]

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

Please Login or Register  to view this content.

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

[cstavr1]

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.

[cstavr1]

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

[Whizbang]

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

[cstavr1]

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!

[Whizbang]

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

Please Login or Register  to view this content.