save file with name equal to cell content (revised)

Got half the answer

Hi 22868,

I've got 1/2 the answer for you...

If:
A1: 15
B1: 5-Oct-05
Add:
C1: _( "replace the underscore with a blank space...
D1: )
E1: =A1&C1&TEXT(B1,"d-mmm-yy")&D1

Then, whenever you change A1 or B1, the value of E1 should look like
ex:
27 (13-Dec-06)

Now for the Macro,

Sub ReNameFile()
ActiveWorkbook.SaveAs FileName:=Range("E1").Value
'value in E1, ex 27 (13-Dec-06)
End Sub

Once you "Run" the macro, the name of you workbook will change automatically from: YourTemplateFile.xls to: 27 (13-Dec-06).xls
Your Template will remain unchanged.

Let me know if it works, as for saving it in a specific path/folder (c:\match results\), you could go to “Tools” > “Options or Properties or something?” a greyish window with about 12 tabs will appear, one of them, “General” I believe, will have a field to input the default location for saving the file. Sorry for the lack of precision here, I’m using a Mac and the steps are a little different from when I use the PC at work.


If you do find a macro to “Save As” with a specific path / folder, let me know.
I’m also trying to do the same, based on... if A, same in Folder A, if B, save in Folder B.
Obviously, the above “Tools” option limits you to 1 field/location.


Cheers,
Sprucy

Hi Sprucy,

Thanks for the macro. Its running like clock work except for the fact that I have to manually run it. Any idea on how to create the file name as soon as I create the workbook from the template?

Thanks in advance,

Pete

Hey Pete,

Sorry, I'm just learning myself.
I'll let you know if I figure it out.

Good luck,
Sprucy

Hi Pete,

Try this one out...
----------------------------
Sub SaveAs()
'specify the name of your sheet
Worksheets("sheet1").Copy
Dim filename As String, path As String, savefile As String
'Change to "S:\....soc\......
path = "c:\match results\" ' add subfolders if needed, but must end with \
filename = Range("E1").Value 'your cell should be??
savefile = path & filename & ".xls"
ActiveWorkbook.SaveAs savefile
'
Workbooks("theNameofyourMasterFile.xls").Saved = True 'True, will NOT saves changes, False will...
Workbooks("theNameofyourMasterFile.xls").Close True
'This will close your master automatically.

End Sub
-----------------------------

By all means, clean-up the macro by removing all excess 'remarks.

Now, if you master file is Games.xls, once you modify the file, Run macro, all changes will be Save As ex:
27 (13-Dec-06)
in the specified path C:\match result\
the final/full path will of course be: c:\match result\27 (13-Dec-06).xls

And Games.xls will close automatically unchanged.