Save and rename

[Steve]

I often want to save a workbook and rename it and wonder if there's a single
step solution. I know I can do a save and then go into the windows explorer
and rename the workbook. Or I can do a save as and then go to windows
explorer and delete the previous version. It would be nice if there was a
way to do it all within excel in a single step.

 

[Gord Dibben]

Only by using code to save as a different name then deleting the original.

What name would you like to "Save As"?

Is there a value in a cell or somesuch?


Gord Dibben MS Excel MVP

 

[Steve]

There's a myriad of situations that might occur. But a common one is where
I've been working on a workbook and want to better describe what I've done
that day or week. For example I may want to add to the end of the filename
something like "upd 2-25-09 w_actuals". Or I started doing something for NJ
and added NY so want to change 2008 NJ to 2008 NJ_NY. It's really so often
that I want to do this but it's always to give a better description of what
the workbook contains without having to sort through a bunch of different
versions and then having to delete them later.

 

[Gord Dibben]

Stick this macro into your Personal.xls

Run it on your current open workbook.

It will pop up the saveas dialog where you enter a name.

The workbook will be saved as that name and original killed(deleted) from
your Computer.

Sub saveas_kill()
Dim oldname As String
oldname = ActiveWorkbook.Name
Set NewBook = Workbooks.Add
fName = Application.GetSaveAsFilename
NewBook.saveas Filename:=fName & "xls"
Workbooks(oldname).Close
Kill oldname
End Sub


Gord

 

[Steve]

Thank you for the reply. The code seems to create a new blank workbook and
saves that which isn't exactly what I wanted. I tweaked your code slightly
and came up with this. It seems to work. Am I on the right track?

Sub saveas_kill()
Dim oldname As String
oldname = ActiveWorkbook.Name
fName = Application.GetSaveAsFilename
ActiveWorkbook.SaveAs Filename:=fName & "xls"
Kill oldname
End Sub

 

[Gord Dibben]

Hope you didn't run that stupid macro on a non-backed up file.

Try this effort which copies the original before killing it!!

Note: if newname already exists you will asked to overwrite or not.

Sub Saveas_Kill()
Dim oldname As String
oldname = ActiveWorkbook.Name
newname = InputBox("Enter a Name")
ActiveWorkbook.SaveCopyAs Filename:=newname & ".xls"
Workbooks(oldname).Close
Kill oldname
End Sub


Gord

 

[Gord Dibben]

Steve

The original code I posted was not good as you found out.

Brain-fart by me.

Yes, you are on the right track with your revision.

See my yesterday's correction posting in this thread for another shot at it.

If you don't see that posting here is the code with an inputbox for entering
a new name instead of pulling up the save as dialog.

Note: if newname already exists you will asked to overwrite or not.

Sub Saveas_Kill()
Dim oldname As String
oldname = ActiveWorkbook.Name
newname = InputBox("Enter a Name")
ActiveWorkbook.SaveCopyAs Filename:=newname & ".xls"
Workbooks(oldname).Close
Kill oldname
End Sub


Gord