auto save on exit while at the same time rename workbook...

[Guest]

Hi,
Is it possible to auto save on exit of workbook, while at the same time
rename the workbook to reflect the contents of cell A1 of sheet1. I want to
auto save a workbook on exit and rename the workbook to the date that appears
in cell A1 of sheet1.

Thank You
Brian

 

[Norman Jones]

Hi Brian,

When the workbook is closed, the following event code will save the workbook
as the date contained in A1 on the first worksheet. As written, the file
will be saved with a name in the format yyyy-mm-dd. this format has the
advantage of allowing the various versions to be sorted chronologically.

Again as written, if A1 is blank, or A1 cannot be intepreted as a valid
date, then the file will *not* be saved and the normal pre-close alert will
be shown asking the user whether changes should be saved (to the existing
file).

You may wish to consider an alternative, default treatment for an empty or
non-date entry in A1.

Note that I have assumed that when the file is saved to the A1 date, the
original file is not updated. If that does not correspond to your intent,
please post back for a suitable variant.

This code should be pasted in the workbook's ThisWorkbook module
(right-click the Excel icon at the extreme left of the menu bar | View
Code):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim rng As Range
Set rng = Me.Sheets("Sheet1").Range("A1")
If IsDate(rng) Then
Me.SaveAs Format(rng.Value, "yyyy-mm-dd")
ThisWorkbook.Saved = True
End If

End Sub

 

[Jason Morin]

Press Alt+F11 and put this the module "ThisWorkbook":

Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wb As Workbook
Dim wbname As String
Set wb = ActiveWorkbook
wbname = Sheets("Sheet1").Range("A1").Value
wb.SaveAs "C:\My Documents\" _
& Format(wbname, "mm-dd-yy") & ".xls"
End Sub