Excel Excel Macro how to prompt for save name

O

Obwan

Joined
Jul 7, 2011
Messages
2
Reaction score
0
Appreciate a little help for a macro novice. Simple macro.
Sheets("SPA").Select
Sheets("SPA").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ChDir "C:\Documents and Settings\me\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\me\Desktop\SPA.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.WindowState = xlMinimized
Windows("PC 2011 Oct master.xls").Activate
End Sub

What and where do I enter a routine to prompt for the save file name. Presently it always save as SPA.xls I would like to be prompted to enter the name. I've seen threads on this but can't get it right. I need someone to say exactly where and what to enter.

Thank you in advance.
 
After this line:
ChDir "C:\Documents and Settings\me\Desktop"

Add the following:

fileName = Application.GetSaveAsFilename
If filename <> False Then

' the name of the file chosen by the user will be stored in fileName
<put your code to save the file here>

End If


Don't forget to add this line at the top of the macro:
Dim fileName as Variant

Hope this helps.

Rajeev
 
Back
Top