MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Can filenames for a Save As command come from a cell?

Posted by chris on January 06, 2001 6:43 AM

I woold like to be able to save files by their date name i.e.August282000 or Monday12112000 which would come from a cell I'ce got in the sheet. Is there anyway to get this info into the Save As box easily?
I am using Templates and it would make sense if the filenaming hapenned automatically

Posted by cpod on January 06, 2001 7:00 AM

You can do this easily in Visual. This line of code will same the file with the name in Sheet1!A1 to the C drive:

ThisWorkbook.SaveAs "c:\" & Worksheets("sheet1").Range("a1") & ".xls"

I don't know how familiar you are with VB but but it's not that hard and we can guide you through the process if you are confused. Do you want to do this from a button on a worksheet or when the file closes.

Posted by chris scott on January 06, 2001 9:01 AM

Yes, Help Please!
A Button would be nice.
To outline the whole problem, it is for accounts for a Charity Shop. We do a daily till sheet (the template) which need to be saved separately.
The information from this sheet needs to go onto a monthly sheet along with other info such as invoices and reports are produced on this data.
Currently the info is all copied manually from one sheet to another as we've only revently upgraded to excel.
I know some of the functions but getting data in and out of sheets is somewhat difficult and restrictive. For example I can't get the template to put the same data in more than one database list - any suggestions would be great!
Many thatnks for your response

Posted by cpod on January 06, 2001 9:37 AM

To put a button on the sheet go to:
Views-> Toolbars -> Visual Basic

On the VB toolbars click on the Tools button (hammer and wrench) and then click on the Command Button button and click someplace on the sheet and button will appear. Double click on that button. Paste this function between the "Private Sub" and "End Sub" lines:

ThisWorkbook.SaveAs "c:\" & Worksheets("sheet1").Range("a1") & ".xls"

You will have to alter this depending on what directory you want to save to and what worksheet cell has the name you want to use. You would change "c:\Backup\" to save to the Backup directory on the C drive. Would change "Sheet1" to the sheet name that contains the cell that has the name you want to save under and change "a1" to the cell on that sheet.

Now close the window for the VB editor. On the VB tool bar click on the Exit Design Mode button (protractor and ruler). The button should now be functional.

I don't quite understand your other questions but if you can elaborate someone here will be able to help you.