I'd like to use this on a macro I am writing. How do I make this work in
Excel 2000?

"David Lloyd" wrote:

The following code shows one alternative for completing this type of
operation. The code makes use of the Dir VBA function to check for the
existence of the file, and the FileDialog class to give the user a way to
alternatively save to a different file name. I don't have enough
information to know when you want to close the file (or files), so you will
have to adjust this to your own needs.

Function SaveWorksheets()
Dim sPath As String
Dim sFileName As String
Dim sh As Worksheet
Dim iResult As VbMsgBoxResult
Dim fd As FileDialog

sPath = ActiveWorkbook.Path

For Each sh In ActiveWorkbook.Worksheets
sFileName = sPath & "\" & "Master " & sh.Name & ".xls"
sh.Copy
If Dir(sFileName) < "" Then
iResult = MsgBox("The default file " & sFileName & " already
exists in " & _
"directory " & sPath & ". Do you want to replace
it?", vbQuestion + vbYesNoCancel, "Save File")
If iResult = vbYes Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs sFileName, xlNormal
Application.DisplayAlerts = True
ElseIf iResult = vbNo Then
Set fd = Application.FileDialog(msoFileDialogSaveAs)
fd.InitialFileName = sPath
If fd.Show = -1 Then fd.Execute
End If
Else
ActiveWorkbook.SaveAs sFileName, xlNormal
End If
Next

Set fd = Nothing

End Function


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


"nuver" wrote in
message ...

Hello
With a macro I need to close my file and save the file as the sheet
name in the current directory. I can accomplish this with the code
below, but if the file exists and the user selects no to the replace
existing file prompt then, I need to prompt the user to enter a new
name to save the file as under the same directory. Once the new name is
entered I need the macro to continue.

Any help is greatly appreciated.

Dim sPath As String
Dim sh As Worksheet
sPath = ActiveWorkbook.Path
For Each sh In ActiveWorkbook.Worksheets
sh.Copy
ActiveWorkbook.SaveAs sPath & "\" & "Master " & sh.Name & _
".xls", xlNormal
Next


--
nuver
------------------------------------------------------------------------
nuver's Profile:
http://www.excelforum.com/member.php...o&userid=10036
View this thread: http://www.excelforum.com/showthread...hreadid=482918