MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Naming a workbook based on Cell Reference

Posted by Melanie on July 25, 2001 10:15 AM


I read on the front page how to name a workbook with the contents of a cell, but I can't get it to work.

Where do I put it? It is a Public Sub and I'm not familiar with those. Should it be in "This Workbook" or should it be in a module? Is there anyway to put it in my opening macro rather than have it as a stand alone?

Posted by faster on July 25, 2001 11:20 AM

This will work change MyFolder to desired directory.

Sub NameBook()

Dim MyName
MyName = ActiveCell.Text

ActiveWorkbook.SaveAs _
FileName:="C:\MyFolder\" & MyName, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _

End Sub

or this will let you select directory

Sub NameBook2()
Dim MyName
MyName = ActiveCell.Text
Application.Dialogs(xlDialogSaveAs).Show _

End Sub