Automatic Save As

[gramps]

What I would like is a macro that onclosing my workbook asks for a date to be
entered by means of an inputbox in ddmmyy format and then automatically saves
the workbook as C:\docs\"form-ddmmyy". Any help would be greatly appreciated.

 

[Mike]

This might not be what you are looking for but try it and let me know
Copy and paste this into the ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook

Again:
fname = Application.GetSaveAsFilename(Format(Now(), "[$-409]mmddyy;@"), _
fileFilter:="Excel Files (*.xls), *.xls")

If fname = False Then Exit Sub
If Dir(fname) <> "" Then MsgBox "File name already exist select a new name",
vbOKOnly
If Dir(fname) <> "" Then GoTo Again


Wb.SaveCopyAs fname
End Sub

 

[gramps]

Thanks Mike but it is not quite what I'm after. What I need is a macro that
when the workbook is closed it opens an input box which asks you to enter a
date. When this is entered it becomes part of the filename and so the path
that is created and saved as C:\docs\report-ddmmyy. This same report is saved
on a daily basis and is therefore distinguihed by the new date.
Thanks again
--
Al

This might not be what you are looking for but try it and let me know
Copy and paste this into the ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook

Again:
fname = Application.GetSaveAsFilename(Format(Now(), "[$-409]mmddyy;@"), _
fileFilter:="Excel Files (*.xls), *.xls")

If fname = False Then Exit Sub
If Dir(fname) <> "" Then MsgBox "File name already exist select a new name",
vbOKOnly
If Dir(fname) <> "" Then GoTo Again


Wb.SaveCopyAs fname
End Sub

What I would like is a macro that onclosing my workbook asks for a date to be
entered by means of an inputbox in ddmmyy format and then automatically saves
the workbook as C:\docs\"form-ddmmyy". Any help would be greatly appreciated.