In Microsoft Excel, I'd like to create a "Save As" macro that I can assign to a button. This macro should let me save my Excel spreadsheet while allowing me to select the file name.
How can I do this?
Answer: The following macro will perform a "File Save As" and then display a message box stating where the file was saved to.
The macro code looks like this:
'Retrieve file name to use for Save
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
'If user specified file name, perform Save and display msgbox
If fileSaveName <> False Then
ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlNormal
MsgBox "Save as " & fileSaveName