VBA to Save File in Todays Date

[thardin]

Hello,
I'm trying to save a file at work on a daily basis in a folder and I want to use VBA.
However, the date in the file name changes daily and there are subfolders for every month and another subfolder for everyday.

The path where I want to save is:
\\nfpgshare-1.ed.com\export\shareholder_acc\629\629 Share Class\Name\Trade\2021

the subfolder would be 01-January, 02-February, etc

The title of the document would be "629 Shares TD mm.dd.yy"

Please include explanations so I can do this in different scenarios, such as if there was a daily folder within the monthly folder.
Thank you

 

[sax2play]

Here is a macro that could work for you. Just update the save directory (currently saves to user profile's desktop) and file format type that you need and you should be all set.

Sub SaveFile()

    Dim NameFile As String

    Range("CW1") = Month(Date)
    Range("CX1") = Day(Date)
    Range("CY1") = Year(Date)
    Range("A1").Select
   
    With Worksheets("[U]ENTER WORKSHEET NAME HERE[/U]")
        NameFile = "629 Shares TD " & .Range("CW1") & "." & .Range("CX1") & "." & .Range("CY1") & ".xlsm"
    End With
    NameFile = Application.GetSaveAsFilename(InitialFileName:=Environ("USERPROFILE") & "\Desktop\" & NameFile, FileFilter:="Excel Macro-Enabled Workbook (*.xlsm),*.xlsm")
    If fileSaveName <> "False" Then
        ActiveWorkbook.SaveAs Filename:=NameFile, FileFormat:=52
    End If

End Sub