See answer in this newsgroup to your later posting of this question.

--
Regards,
Tom Ogilvy


"WillRn" wrote in message
...
Dave,

Thanks for the code it works like a charm. I do have an additional
question
however.

Is there a way to get it to save the file under a different name and then
return to the original workbook.

For example, Save it as "PFSNov_Copy.xls" but return to "PFSNov.xls" as
the
active workbook?

"Dave Peterson" wrote:

I suggested this for a similar post:

Option Explicit
Sub testme01()
Dim myFileName As Variant
Dim OkToSave As Boolean
Dim resp As Long

Do
myFileName = Application.GetSaveAsFilename _
(filefilter:="Excel files, *.xls")
If myFileName = False Then
Exit Sub
End If

OkToSave = True
If Dir(myFileName) = "" Then
'do nothing special
Else
resp = MsgBox(prompt:="Overwrite Existing file?", _
Buttons:=vbYesNoCancel)
Select Case resp
Case Is = vbCancel
MsgBox "Try again later"
Exit Sub
Case Is = vbNo
OkToSave = False
End Select
End If

If OkToSave Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=myFileName, _
FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True
Exit Do
End If
Loop

End Sub


WillRn wrote:

I am trying to modify a macro to enable my user to save a file locally
on
their hard drive with a name and location that they specify. I keep
getting
an "End if without Block If" compile error. Being very new to VBA
code, I
can't seem to get the macro to work.

It is as follows:

Private Sub SaveMe_Click()
If NewName < False Then
If Dir(NewName) < "" Then Select Case MsgBox("File Exists.
Overwrite
?", vbYesNoCancel + vbQuestion)

Case vbYes
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=NewName,
FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True

Case vbNo
Do
NewName = Application.GetSaveAsFilename( _
InitialFileName:=ActiveWorkbook.Path & "\" & NameAk,
FileFilter:="Excel Workbooks (*.xls), *.xls")
If NewName = False Then Exit Sub
Loop Until Dir(NewName) = ""
ActiveWorkbook.SaveAs Filename:=NewName,
FileFormat:=xlWorkbookNormal
Case Else
Exit Sub
End Select
Else
ActiveWorkbook.SaveAs Filename:=NewName,
FileFormat:=xlWorkbookNormal
End If
End If
End Sub

I know it is probably very simple, but I just can't seem to find the
problem
. . .

WillRn

--

Dave Peterson