Date in Filename

I am currently writing a macro for use in Excel, which selects a range of cells, pastes them into a new sheet and saves them. What I would like it to do, is have the macro include the date in the filename... is this possible, and if so, how?

You can use the

VB Code:

Format()

function with

VB Code:

Date

to get the current date into the filename.

VB Code:

strFileName = "MyFilePrefix_" & Format(Date,"yyyy-mm-dd")

will return MyFilePrefix_2004-10-13.

I've got this so far, but it either returns an error, or saves the file as FALSE.xls

Code:

---

Sub ExportSheet()
    Cells.Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs strFileName = "MyFilePrefix_" & Format(Date, "yyyy-mm-dd")
End Sub

---

Any advice?

You've tried to do 2 operations on the same line, change your code to read :-

VB Code:

Sub ExportSheet()
    Cells.Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Range("A1").Select
    Application.CutCopyMode = False
    strFileName = "MyFilePrefix_" & Format (Date, "yyyy-mm-dd")
    ActiveWorkbook.SaveAs strFileName 
 End Sub

i dont know if you need to include the file extension in the name aswell or if you can get away with out it, but if you need it just change:-

strFileName = "MyFilePrefix_" & Format (Date, "yyyy-mm-dd")

TO:

strFileName = "MyFilePrefix_" & Format (Date, "yyyy-mm-dd") & ".xls"