save as = cell

[ksmith]

I want to save a worksheet by clicking a button that will save the sheet as the text in a certain cell. So the macro would have to save as the text in a cell.
example: cell A1=6315. Save file as 6315.
Any ideas would be great! Thanks!

 

[Joe Was]

Sub SaveByCell()
Dim sFileName As String
'Standard module code!
'Cell A1 has the new file name.
sFileName = Sheets("Sheet1").Range("A1")

'If Cell A1 is Blank do not save.
If sFileName = "" Then Exit Sub

'The Drive:Path & the file name in cell A1.
ThisWorkbook.SaveAs "C:MyFilesJSWExcelData" & sFileName
End Sub

 

[zacemmel]

Nevermind ;p
This message was edited by zacemmel on 2002-10-28 15:39

 

[lenze]

Try one of these macros previouly posted on the board.

Sub NameBook()

Dim MyName
MyName = ActiveCell.Text

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

End Sub
or this will let you select directory
Sub NameBook2()
Dim MyName
MyName = ActiveCell.Text
Application.Dialogs(xlDialogSaveAs).Show _
MyName
End Sub

If you want to fix the code to a specific cell, change ActiveCell.Text to Range("$A$1").Text

 

[ksmith]

Thanks everyone! I tried a couple and they both worked great!