Save file to a new name using excel vba

[OALes]

Hello, and thank you for your help!
I need to add a section to my sub in excel 2016 vba that will go to a new folder and name my new file based on a preset naming confvention changing the date included as the previous date.

Every morning I export a file which tells us what we want to know about a certain subject. I have a macro that reformats, adds new information, sets a print area and margins, now I need to save it. This file goes in the same folder every day and is named the same thing except for the date. I need to use the previous date because it refers to the data collected on that day.

I would like to move to the appropriate folder and name the file using an input box to set the date in question.

Can you help?

 

[PeteWright]

Hi @OALes

You did not provide the folder in which the workbook should be saved and the date format.
The below VBA code will save a copy of your current (active) Workbook to the same location adding the date you specified as suffix (formatted as -YYYYMMDD).

Option Explicit

Sub SaveWithDate()
   Dim fPath As String: fPath = Application.ActiveWorkbook.Path
   Dim fName As String: fName = Application.ActiveWorkbook.Name
   Dim fDate As String
   Dim fExtn As String
   Dim fNnew As String
   Dim DateStr As String
   Dim d As Date
   
   Do Until IsDate(DateStr)
      DateStr = InputBox("MM/DD/YYYY", "Please enter a date")
   Loop
   d = CDate(DateStr)
   fDate = Format(d, "yyyymmdd")
   fExtn = Right(fName, Len(fName) - InStrRev(fName, ".") + 1)
   fName = Left(fName, InStr(fName, ".") - 1)
   fNnew = fPath & "\" & fName & "-" & fDate & fExtn
   Application.ActiveWorkbook.SaveCopyAs FileNAME:=fNnew
End Sub

Please let me know the above stated details and I'll edit the code to suit your needs.