Macro to save book with diferent name each time ran

[mal]

Need macro or VBA code to automatically store the work sheet with a new name each time ran. Could use a counter to add to the name or add the date to the name.

Thanks, Mal

[Yongle]

This will achieve what you want and guarantee each filename will be unique even if saved many times on the same day.
- NOW() is formatted as a string without characters not allowed in filename (\ etc)
- TIMER counts the number of seconds since midnight
- combined they provide a unique suffix to the filename

Amend UniqueName to whatever the filename should begin with
Amend d:\documents\ to the path that the files should be saved to
If the file is to be closed following being saved - remove the apostrophe before ActiveWorkbook.Close



Option Explicit
Sub SaveAsNewName()
Dim WhatEver As String, Path As String, FileName As String, ThisIsNow As String
WhatEver = "UniqueName"
Path = "d:\documents\"
ThisIsNow = Format(Now(), "yyyymmdd")
FileName = WhatEver & ThisIsNow & Timer & ".xlsm"
MsgBox FileName
ActiveWorkbook.SaveAs Path & FileName
'ActiveWorkbook.Close
End Sub

[Yongle]

Or using a simple counter (for illustration placed in cell A1 on sheet1)
Formatting variable FileNumber with Format(FileNumber, "00000") ensures file name lengths are consistent. Name00001.xlsm - avoids Name1.xlsm , Name10.xlsm Name100.xlsm etc
Amend UniqueName to whatever filename should begin with
Amend d:\documents\ to the path that the files should be saved to


Sub SaveAsNewNameB()
Dim WhatEver As String, Path As String, FileName As String, FileNumber As Long
Path = "d:\documents\"
WhatEver = "UniqueName"
Sheet1.Range("A1") = Sheet1.Range("A1") + 1
FileNumber = Range("A1").Value
FileName = WhatEver & Format(FileNumber, "00000") & ".xlsm"
MsgBox FileName
ActiveWorkbook.SaveAs Path & FileName
End Sub

[mal]

Thank you very much. I will use one of them.
Mal

[mal]

Thank you again, I am using your first suggestion. Problem solved.

[mal]

PROBLEM SOLVED. THANKS EVERYBODY