[SOLVED] VBA - save workbook and add today's date to current workbook name

VBA - save workbook and add today's date to current workbook name

The code below shows my save macro. Right now it saves the current workbook in a specific file path with the current workbook name. How can I add today's date in the current workbook name? So it saves to the designated file path with the current workbook name and today's date on the end?

Sub Save_Workbook()
    ActiveWorkbook.SaveAs "H:\HR\Username\Timehseet Test Path\" & ActiveWorkbook.Name
End Sub

Solution

First off, .Name may or may not include a file extension, depending on if the file has been saved or not. (eg. "Test.xls" or "Book2")

Const Path = "H:\HR\Cole G\Timehseet Test Path\"
Dim Pos as Long

Pos = InStrRev(ActiveWorkbook.Name, ".") - 1
' If there wasn't a ".", then the file doesn't have an extension and Pos = -1
If Pos < 0 then Pos = Len(ActiveWorkbook.Name)
' Now put everything together, including the file extension...
ActiveWorkbook.SaveAs Path & Left(ActiveWorkbook.Name,Pos) & Format(Now, "yyyy-mm-dd") & Mid(ActiveWorkbook.Name,Pos+1)