Questions, Questions

[Frankie needs HELP]

I need to produce a message box containing two buttons (Yes and No.

When you click on the Yes I need to make my spreatsheet ("Analysis") move to a new workbook and the user prompted to enter a filename where the workbook should be saved, including allowing saving to a different folder or drive. It should also automatically close the macro down.

If No is clicked, the macro should simply terminate, and leave the worksheets visable.

HELP!!!

[Frankie needs HELP]

Ok then make it simpler. How do I get two buttons, Yes and No, with a question: Would you like to save?

[AJ]

Ok, to answer the first part, the message box can be done like this...

Sub test()

Dim MyCheck

MyCheck = MsgBox("Would you like to save?", vbYesNo)
If MyCheck = vbYes Then
' put what to do if they say yes here
ElseIf MyCheck = vbNo Then
' put what to do if they say no heer
End If


End Sub

As to the rest, you can probably record most of the copying your sheet to a new workbook bit, and then you might find help on the SaveAs part here...
http://www.mrexcel.com/board/viewtop...4684&forum=2&4

[Al Chara]

Try the following:

Sub SaveWorkbook()
messagebox = MsgBox("Export Worksheet", vbYesNo)
If messagebox = vbYes Then
Dim NEWBOOK, FNAME
Application.SheetsInNewWorkbook = 1
Set NEWBOOK = Workbooks.Add
FNAME = Application.GetSaveAsFilename _
(InitialFilename:="NewWorkbook")
If FNAME <> "False" Then
NEWBOOK.SaveAs Filename:=FNAME
End If
ThisWorkbook.Activate
Sheets("Analysis").Copy Before:=NEWBOOK.Sheets(1)
Application.DisplayAlerts = False
NEWBOOK.Worksheets("Sheet1").Delete
End If
GoTo 1

1 End Sub

[Frankie needs HELP]

Al, the line (InitialFilename:="NewWorkbook") is coming up with an error, Expected:line number or label or statement or end of statement comes up. What am I doing wrong?

[Frankie needs HELP]

I'm still struggling. I've got the yes no message popping up and when I click yes it puts the Analysis worksheet into a new work book but I also need a prompt to enter a filename where the workbook should be saved, and also allowing saving to a different folder or drive. HELP!

[Al Chara]

Try the following, I got rid of that line (it works fine for me, XL2000):

Sub SaveWorkbook()
messagebox = MsgBox("Export Worksheet", vbYesNo)
If messagebox = vbYes Then
Dim NEWBOOK, FNAME
Application.SheetsInNewWorkbook = 1
Set NEWBOOK = Workbooks.Add
FNAME = Application.GetSaveAsFilename
If FNAME <> "False" Then
NEWBOOK.SaveAs Filename:=FNAME
End If
ThisWorkbook.Activate
Sheets("Sheet1").Copy Before:=NEWBOOK.Sheets(1)
Application.DisplayAlerts = False
NEWBOOK.Worksheets("Sheet1").Delete
End If
Application.DisplayAlerts = True
End Sub