Save As Code
 

JT,

You may need more sophisticated error checking: does the file already exist, etc. but this will give
you an idea of where to start.

HTH,
Bernie
MS Excel MVP


Sub SaveFileWithUsersFileName()

Dim newFName As String

newFName = Application.GetSaveAsFilename

If newFName = "False" Then
MsgBox "You pressed cancel"
Exit Sub
End If

If InStr(1, newFName, ".xls") = 0 Then
newFName = newFName & ".xls"
End If

ActiveWorkbook.SaveAs Filename:=newFName, FileFormat:=xlNormal

End Sub


"JT" wrote in message
...
In my macro, I want to activate a certain workbook; which is no problem.

Workbooks(vOldFileName).Activate

What I want to do next is to open the save as dialog box and let the user
select a name for the "new file". Once they they have clicked the "save"
button, I will capture the active workbook name.

vNewFile = activeworkbook.name

I'm having trouble with the save as. Below is the code, I got when I
recorded a macro to use save as. The problem is I don't know what the user
will name the new file and the info below is what I called it when I recorded
the macro.

ActiveWorkbook.SaveAs Filename:="C:\Test\2007.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Any help with the code to display the save as dialog box, would be greatly
appreciated. Thanks for the help....

--
JT