using macro to save worksheet with unique file name

  • Thread starter Thread starter Noell
  • Start date Start date
N

Noell

I am trying to write a macro to save a worksheet with a
unique file name, preferably using a cell value, every
time the macro is used. Can anyone help me?
 
Hi

Why would a cell value be unique whenever you run the code ? Anyway, here's one that uses
a cell and also date/time. Unique unless you run it twice in the same second:

Function UniqName() As String
UniqName = Sheets(2).Range("B3").Value & _
Format(Now, "yymmddhhmmss") & ".xls"
End Function

Sub test()
MsgBox "Save me as " & UniqName
End Sub
 
Activeworkbook.saveas cells(1, 1)
whatever is cell A1 will now be the filename. You can enter that into the
workbook_beforeclose event and that way before the workbook is closed it
will save it as the new name.
 
Hi Noell

You can use the date and time as a part of the filename

Sub Test()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Set wb = ActiveWorkbook
With wb
.SaveAs Sheets("Sheet1").Range("A1").Value _
& " " & strdate & ".xls"
'wb.Close False
End With
End Sub
 
Back
Top