The VBA script below saves a sheet to xlsx file with today's date in the name.
Use the GetSaveAsFilename function:
'-------------------------------------------------------------------------
' Source: risksir.com
' Description: Saving Sheet in .xlsx File
'-------------------------------------------------------------------------
Sub SaveSheet()
On Error Resume Next
ThisWorkbook.Worksheets("Sheet1").Activate
Const REPORTS_FOLDER = "My Reports\" 'Folder to save our file
MkDir ThisWorkbook.Path & "\" & REPORTS_FOLDER
ChDrive Left(ThisWorkbook.Path, 1): ChDir ThisWorkbook.Path & "\" & REPORTS_FOLDER
TbName = "MyFilename" & Format(Date, "YYYYMMDD") 'FileName plus today's date
filename = Application.GetSaveAsFilename(TbName, "Excel (*.xlsx),", , "Name", "Save")
If VarType(filename) = vbBoolean Then Exit Sub
Err.Clear: ActiveSheet.Copy: DoEvents
If ActiveWorkbook.Worksheets.Count = 1 And ActiveWorkbook.Path = "" Then
ActiveSheet.Buttons.Delete 'deleting buttons
ActiveSheet.Range("A:Z").Value = Range("A:Z").Value 'cleaning formulas
ActiveWorkbook.SaveAs filename, FileFormat:=51 'setting .xlsx format
ActiveWorkbook.Close False
End If
End Sub