Save as to create a new folder and workbook based of a master workbook

[jmyers1]

I need a dynamic batch file written to create all of Joe's timesheets for the year.
Sunday is the end of the pay period.
For example the Sunday in January 2019 is the 5th
The file name would be 20190105 the folder will be named c:\Timesheet\2019\01-January\
The complete file path would be --> c:\Timesheet\2019\01-January\20190105-timesheet.xlsx
You can use excel and concatenation to dynamically build the file name and file path for the batch file.
This should all be accomplished using two columns in excel one for the week ending date and one for the formula.
This same process will need to occur each week and include the first pay period of 2020.
The attached excel file is the file you will copy to create each weekly.
The excel file should be smart enough to dynamically build the file by entering 1/1/2019.
When 1/1/2020 is entered it should create the same script for next years data.

I have another excel workbook called TS.xlsx that links to the master with concatenation of the date to text. for the next 53 weeks. I just haven't figured out how to link it to master to save as the above referenced path and filenames.

 

[jmyers1]

Here is the TS example:

A1: Date format B1: ="Copy TS.xlsx c:\Timesheet\2019\01-January\Timesheet_"&RIGHT(C1, 4)&RIGHT(D1,2)&RIGHT(E1,2) D1: =TEXT(A1, "yyyy) E1: =TEXT(A1, "mm") F1: =TEXT(A1, "dd")