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.

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.

"kdreyer" wrote:

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.

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.

--
Regards,
Tom Ogilvy


"kdreyer" wrote in message
...
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.

"kdreyer" wrote:

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.