Save workbook with today's date at the end

[slimpickens]

Hi Guys,

Can anyone tell me how I can save the workbook with today's date at the end of it?

I have a message asking if today is the last entry for the week. If yes I want the workbook saved as motor19/04/02. Is this possible? It could be motorApr192002 or anything that would identify today's date.
I can't seem to get pass this point.
Any help would be appreciated.

Thanks

Carol

[ This Message was edited by: slimpickens on 2002-04-19 07:40 ]

[Joe Was]

The code go's in "ThisWorkbook." Note: you will need to hardcode your file name with a "-" at the end below!

This code will, each time you open the file, save the file with the date at the end of the file name. Hope this helps. JSW

Private Sub Workbook_Open()
Dim MyDate
Dim MyMonth
MyDate = Date
MyMonth = Month(MyDate)
ThisWorkbook.SaveAs Filename:="YourFileNameHere-" & MonthName(MyMonth) & "-" & Day(Date) & "-" & Year(Date)
End Sub

[slimpickens]

Hello JSW,

I was sort of on the right track with my code, thanks for your help. I used this in a commandbutton on my form and it is working to a certain extent. I substituted the SaveAs for SaveCopyAs. Let Me Explain.

I have on CommandButton2 -
If vbyes then
CommandButton3_Click

This is working fine up to the point where I want to have my original document "motor" closed and left blank with just the column headings for the next weeks entries.

The code I have for CommandButton3 is

Dim MyDate
Dim MyMonth
MyDate = Date
MyMonth = Month(MyDate)
ThisWorkbook.SaveAs Filename:="YourFileNameHere-" & MonthName(MyMonth) & "-" & Day(Date) & "-" & Year(Date)
ThisWorkbook("Sheet1").Range("A5:V50").ClearContents
End Sub


Am I using this piece of code correctly??

Carol

[Joe Was]

The code will save the original workbook as the name and date. The old workbook will now be untouched all your future work will go into the current workbook, the one with the date at the end.

If you set up your master workbook as a template, that is a standard workbook with all the required formating, not a true Excel template. Then the code you are using will keep the pseudo-template workbook blank for the next time.

When building your pseudo-template workbook, get it the way you want it, then add the auto-save-re-name code. Or, rename your best version, your master name and delete all the bad copies. Then your application should be ready for production.

If you use a date named workbook you will get a workbook with two or more names at the end. JSW

[ This Message was edited by: Joe Was on 2002-04-22 18:27 ]