Excel 2010 - Copy workbook to another location & rename with existing workbook name but add date & time

[PWG]

Hi - cant get this to work..

Firstly though I have put in the folder location as below to the desktop the workbook copy keeps only going to "my documents"?..also I want to add the date & time to the copied workbook to stop it over-riding previous copies - any ideas?

Sub TESTsave_copy_of_workbook()
ActiveWorkbook.Save
ActiveWorkbook.SaveCopyAs Filename:="C:\Documents and Settings\Peter\Desktop" & ActiveWorkbook.Name
End Sub

Thanks

 

[drsarao]

Something like this:

Sub TESTsave_copy_of_workbook()
fPath =  "C:\Documents and Settings\Peter\Desktop[COLOR="#FF0000"]\[/COLOR]"
DateTimeStamp = Format(Date, "yyyymmdd-") & Format(Time, "hh.mm.ss - ")
ActiveWorkbook.Save
ActiveWorkbook.SaveCopyAs FileName:=fPath & DateTimeStamp & ActiveWorkbook.Name
End Sub

Will save the copy as eg "20120818-14.14.50-Book2.xls"