MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Need to save new copied sheet

  Asked By: Lydia    Date: Oct 03    Category: MS Office    Views: 731

I have a macro that takes a sheet and copies it to a new workbook. I
would like for a MsgBox (vbYesNo) to appear next that asks the user if
he wants to save the new document.

If the user clicks YES, then the Save As window would appear which
would allow the user to enter filename and designate the filepath,
etc. Once the user clicks on Save or Cancel then the VBA code resumes
from there.

Else If the user clicks NO, then Next (VBA resumes...)

Can anyone provide an example on how to code this? I'm not sure how to
get the Save As box to pop up...and then also have my VBA resume
execution from there.



2 Answers Found

Answer #1    Answered By: Joann Gardner     Answered On: Oct 03

try this:

Sub test()
Dim Result, fileSaveName
Result = MsgBox("Do you wish to save?", vbYesNo, "Save")
If (Result = vbYes) Then
fileSaveName = Application.GetSaveAsFilename(filefilter:="Excel files
(*.xls), *.xls")
If fileSaveName <> False Then
ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlNormal,
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
End If
End If
MsgBox "continuing"
End Sub

Answer #2    Answered By: Ty Thompson     Answered On: Oct 03

I left out a couple of considerations in my previous example.
For instance:

Keep in mind that your macro  is running in the first file,
but when you copy a sheet  to a new workbook, the "focus"
changes to the new workbook.
Remember that Excel uses "ThisWorkbook" to mean the
workbook which has the macro currently running.
"Activeworkbook" is the workbook  that currently has "focus".
So, let's say that your Excel file with macros is called "File1.xls".
When the sheet is copied  to a new book, and the user  decides to
"saveAs" and calls the new book "NewFile.xls".
Thisworkbook.name is "File1.xls"
Activeworkbook.name is "NewFile.xls".

Just to keep it straight...

Didn't find what you were looking for? Find more on Need to save new copied sheet Or get search suggestion and latest updates.