Name a Workbook with the Text in a Cell
When you save your workbook, use the text of a selected cell as your filename.
It's quite common for Excel users to want to save an Excel file with a filename that corresponds to the text in a worksheet cell. This can be done with the help of a small amount of code inserted into a module.
Click in cell C1 on the worksheet and enter some text to save. We will use the codename Sheet1.
Open the Visual Basic Editor by selecting Developer → Code → Visual Basic, or right-clicking on the sheet name and selecting ViewCode (pre-2007, Tools → Macros → Visual Basic Editor), or pressing Alt/Option-F11.
Select Insert → Module and paste the following code:
Sub SaveAsCell( ) Dim strName As String On Error GoTo InvalidName strName = Sheet1.Range("C1") ActiveWorkbook.SaveAs strName Exit Sub InvalidName: MsgBox "The text: " & strName & _ " is not a valid file name.", vbCritical, "Ozgrid.com" End Sub
Now, click the top-right X (or press Alt-Q) to get back to Excel proper, and then save your workbook.
Click anywhere on your worksheet and select Alt/Option-F8. Then, select the SaveAsCell macro from the dialog, click Run, and your workbook will automatically be saved as Sheet1. If you had a name other than Sheet1 in cell C1, the code will still work and your workbook will be saved as that name. The only time the code will fail is if you have an invalid filename in cell C1.
In this tutorial:
- Retrieve a Workbook's Name and Path
- Excel's Three-Criteria Limit for Conditional Formatting
- Run Procedures on Protected Worksheets
- Distribute Macros
- Automatically Add Date/Time to a Cell upon Entry
- Create a List of Workbook Hyperlinks
- Find a Number Between Two Numbers
- Name a Workbook with the Text in a Cell
- Sort Worksheets
- Password-Protect a Worksheet from Viewing