How to prompt for "Save As"

[promsi]

At the end of the macro I want the macro to perform a File Save As - and have the user pick the location and name of the file.

 

[VoG]

Try

Dim fName As Variant
fName = Application.GetSaveAsFilename(initialFilename:="", ileFilter:="Excel Files (*.XLS), *.XLS", Title:="Save As")
If fNameAndPath = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=fName

 

[Richard Schollar]

Hi

Something like:

With Application.FileDialog(msoFileDialogSaveAs)
  If .Show=-1 Then .Execute
End With

 

[promsi]

Thanks for the help. VoG it comes back with an error that ileFilter is Named Argument not Found.

Here is the end of my macro that i want to perform the File / Save As.

(I don't know how to insert a "Code Box" into my post - sorry)

Dim fName As Variant
Range("A1:X" & f_RowCount + 2).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
fName = Application.GetSaveAsFilename(InitialFileName:="", ileFilter:="CSV (*.csv)", Title:="Save As")
If fNameAndPath = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=fName
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

 

[VoG]

Sorry, that was a typo. It should be FileFilter

 

[promsi]

Ok - now it compiles but there is an error when it gets to that line.

Method 'GetSaveAsFilename' of object '_Application' failed

Thanks

 

[VoG]

That's strange. Have you tried Richard's alternative?

 

[promsi]

Thanks all - I just tried Richards example and it worked great.