Save As file name + Today's Date using a macro

[roryt]

Please could someone tell me how to use a macro to Save a worksheet as the
filename + the date and then save it as it's previous name again. This is
to create a backup copy of the spreadsheet and then let the user work on the
previous spreadsheet from it's original filename. I am pretty sure this has
got to be in VBA. Here is the code I have found to save as filename+Date:

Private Sub SaveDatabaseButton_Click()
ActiveWorkbook.SaveAs Filename:= _
“F:\User Form for Lessons Learned\Lessons Learned †_
& Format(Date, “yyyymmdd “) & Format(Time, “hh.mm.ssâ€) & “.xlsâ€, _
FileFormat:=xlNormal, Password:=â€", WriteResPassword:=â€",
ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub

But because I am totally new to VBA I can't even get that to work!!! So i
really need someone to tell me exactly to get from start to finish (finish
being a point where I have a macro that will do what I have explained above).

If anyone can help I will be very very grateful. Thanks

 

[MaryLindholm]

Here is what I did on one of my macros. I needed it to save with the
current date and also save as it was:

Sub test()

ActiveWorkbook.SaveAs Filename:= _
"F:\User Form for Lessons Learned\Lessons Learned " &
Format(Now(), "mm-dd-yyyy") & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

ActiveWorkbook.SaveAs Filename:= _
"F:\User Form for Lessons Learned\Lessons Learned.xls",
FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

End Sub

I hope that helps.

 

[PCLIVE]

You should just be able to save the file first, and then resave to the name
with the date. Just add this before the 'SaveAs' line.
ActiveWorkbook.Save


HTH,
Paul

 

[Alok]

What is the error you are getting in the Macro?

 

[Bob Phillips]

With ActiveWorkbook
.SaveAs Filename:="F:\User Form for Lessons Learned\Lessons Learned " &
_
Format(Now, "yyyymmdd hh.mm.ss") & ".xls"
.SaveCopyAs "C:\TEMP\XXXX.XLS"
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[ZootRot]

One of the benefits of saving a filename with a date is being able to sort
by date.
It is, therefore, preferable to save with the format yyyymmdd or a variation
thereof.