Save As

[Guest]

I am trying to set up a macro in a workbook so that when I run the macro the
file will Save As and prompt for a new file name. I would like to have the
old file name show in the box as I only need to change a few caracters.

 

[Guest]

I also need to switch back and forth between two workbooks with this same
macro. After I have specified the file name is there any way that the rest
of the macro will be able to call the workbook with the new name.

 

[Tom Ogilvy]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

Dim filesavename as Variant
filesavename = Application.GetSaveAsFilename( _
ThisWorkbook.Filename, _
fileFilter:="Microsoft Excel Workbook (*.xls),*.xls")

If filesavename <> False Then
Application.EnableEvents = False
if lcase(thisworkbook.fullname) = lcase(filesavename) then
thisworkbook.Save
else
on Error resume Next
kill filesavename
on Error goto 0
thisworkbook.SaveAs filesavename
end if
End If
Application.EnableEvents = True
Cancel = True
end sub

Put this in the ThisWorkbook module of each workbook.

In you code refer to the book containing the code as thisworkbook, and you
won't need the name.

for the workbook with the focus, use ActiveWorkbook.