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

You will have to change this bit:

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

to something like:

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

Hope this helps.

Pete

On Mar 22, 11:39 am, Stephanie
wrote:
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

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?

"Pete_UK" wrote:

You will have to change this bit:

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

to something like:

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

Hope this helps.

Pete

On Mar 22, 11:39 am, Stephanie
wrote:
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

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" wrote:

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

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!

"Mike" wrote:

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" wrote:

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

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

Pete

On Mar 22, 12:58 pm, Stephanie
wrote:
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!



"Mike" wrote:
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" wrote:

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- Hide quoted text -

- Show quoted text -

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)
--
THANKS!

Steve


"Pete_UK" wrote:

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

Pete

On Mar 22, 12:58 pm, Stephanie
wrote:
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!



"Mike" wrote:
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" wrote:

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- Hide quoted text -

- Show quoted text -

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

On Wed, 2 May 2007 16:29:01 -0700, Steve_n_KC
wrote:

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)