Macro to "Save As"

[Stephanie]

Hi. I'm trying to create a macro to save as a new document, but I keep
getting a run-time error message when I run it. What it's supposed to do is
ask the user if they want to save it as a new document, if yes, then it
should go through the normal save as procedure (and I'm trying to point it to
a specific directory), if no, then the spreadsheet will just save. Here's
the code I have written. I'm no expert, I'm really just learning this as I
go, so please be gentle. Thanks!

If MsgBox("Do you want to Save this to a new file?" _
, vbYesNo, "Save As") = vbYes Then
ActiveWorkbook.SaveAs Filename:="S:\Sales\.xls"
Else
ActiveWorkbook.Save

 

[Pete_UK]

You will have to change this bit:

Filename:="S:\Sales\.xls"

to something like:

Filename:="S:\Sales\your_name.xls"

Hope this helps.

Pete

 

[Stephanie]

Thanks Pete. That worked great, it's saving in the right place, but now it
saves as a document called "your name". Is there any way I can get it to
allow the user to actually name it themselves before it saves?

 

[Mike]

Stephanie,

Try this

Sub saveit()
response = MsgBox("Do you want to Save this to a new file?" _
, vbYesNo, "Save As")
If response = 6 Then
Name = InputBox("Enter a Filename", "Get Filename")
If Name = "" Then End
ActiveWorkbook.SaveAs Filename:="C:\" & Name & ".xls"
End If
End Sub

 

[Stephanie]

Thanks Mike. Between your post and Pete's I took the best of both and got it
to run the way I needed it to. Thank you both so much!

 

[Pete_UK]

You're welcome, Stephanie, thanks for feeding back.

Pete

 

[Steve_n_KC]

Is it possible to perform the exact same task but to either strip the macros
out of the newly saved documents? Or delete a "Save New Form" button that I
want to assign this macro to out of the new file? Or IDEALLY to delete 1
worksheet out of the workbook before saving the new one? (intended use is in
a "user friendly" form generating template)

 

[Gord Dibben]

Steve

See Chip Pearson's site for deleting code from a workbook using VBA.

http://www.cpearson.com/excel/vbe.htm


Gord Dibben MS Excel MVP

 

[Steve_n_KC]

As always...works like a top now!