Automatically generating duplicate workbooks renamed with incremental dates

[K80flo]

Hi, I'm used to basic Excel usuage but I'm trying to do nothing which I know is possible but I just can't work out how to do it.
Please can someone help.....

I have a Master Copy Template Workbook with a date in A1 I want to automatically generate identical sequential editable workbooks BUT with a new date for each..
i.e. 1st Workbook NAMED 01.04.2018 with the date also in A1 the next NAMED 02.04.2018 with the date in A1. etc:

All the cells in the workbook relate to data input for activity for that day. A workbook needs to be kept for each day for audit purposes. The workbook has been designed to be simple to use as multiple non IT savvy people will access to input their data into specific cells & save.
There needs to be a new workbook generated for each day as it is a log of activity for that day.

Please help
Thanks

 

[NdNoviceHlp]

Hi K80flo and Welcome to the Board! Give this a trial. HTH. Dave

Sub CopyDateFile()
Dim Ofsobj As Object, NowStr As String
Set Ofsobj = CreateObject("Scripting.FileSystemObject")
NowStr = Format(Now, "yyyy/mm/dd")
ThisWorkbook.Sheets("sheet1").Range("A" & 1) = NowStr
Ofsobj.CopyFile ThisWorkbook.FullName, Left(ThisWorkbook.FullName, _
   Len(ThisWorkbook.FullName) - 5) & "_" & NowStr & ".xlsm", True  'source,destination,save
Set Ofsobj = Nothing
End Sub

 

[Sallyfomthevalley]

Hi K80flo and Welcome to the Board! Give this a trial. HTH. Dave

Sub CopyDateFile()
Dim Ofsobj As Object, NowStr As String
Set Ofsobj = CreateObject("Scripting.FileSystemObject")
NowStr = Format(Now, "yyyy/mm/dd")
ThisWorkbook.Sheets("sheet1").Range("A" & 1) = NowStr
Ofsobj.CopyFile ThisWorkbook.FullName, Left(ThisWorkbook.FullName, _
   Len(ThisWorkbook.FullName) - 5) & "_" & NowStr & ".xlsm", True  'source,destination,save
Set Ofsobj = Nothing
End Sub

Good evening!

Could I also pick your knowledge please?

I have created a timesheet within excel and would like to add a command button named ' new timesheet ' which does exactly as it states.. opens a new blank timesheet.

I am sure this is a simple command, but I am really struggling to find the correct code, any help would be appreciated.

Many thanks

 

[NdNoviceHlp]

This request doesn't really seem related to the thread topic. This thread creates a copy of the file/workbook with a new name based on date. I assume it works as K80flo apparently had other urgent matters to attend to rather than providing feedback and/or acknowledging the help received. The code doesn't open anything. If U want a copy of the current workbook timesheet to be generated and open up blank, I would suggest using the above code to copy it to some location then opening it with VBA and "blanking" it. Please start a new thread. As a suggestion, please be clear with your request. A new sheet is not the same as a new workbook. U need to include what file path U want this new workbook to be created if that's what U want to do. HTH. Dave

 

[K80flo]

Thank you for your help

NdNoviceHlp I'm sure your code would probably work but my understanding of code is that limited that I don't think I'm inserting my detail of my workbook into it sufficiently.
This is not my normal thing & I'm trying to self teach & it is going very slowly.​

 

[Fluff]

Try this mod on NdNoveHlp code

Sub CopyDateFile()
Dim Ofsobj As Object, NowStr As String
Set Ofsobj = CreateObject("Scripting.FileSystemObject")
NowStr = Format(Now, "[COLOR=#ff0000]yyyy-mm-dd[/COLOR]")
ThisWorkbook.Sheets("sheet1").Range("A" & 1) = NowStr
Ofsobj.CopyFile ThisWorkbook.FullName, Left(ThisWorkbook.FullName, _
   Len(ThisWorkbook.FullName) - 5) & "_" & NowStr & ".xlsm", True  'source,destination,save
Set Ofsobj = Nothing
End Sub