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

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

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

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

---
Regards,
Norman



"Brian" wrote in message
...
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

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

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
---

HTH
Jason
Atlanta, GA

-----Original Message-----
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
.