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.

The Code

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.