I want a macro to save a workbook as a user inputted cell

E

EAHRENS

I want to use a macro to automatically save a workbook as a name the user
will be required to enter into a cell. How can i do this?
 
A

Alan

To save the file and call it whatever is in Sheet1, range A1 try,
Sub SaveAsCellValue()
ThisWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Owner\My Documents\" & Sheet1.Range("A1") &
".xls"
End Sub
Change the file path to suit,
Regards,
Alan,
 
E

EAHRENS

I pasted that in nad changed sheet1 to intro and range a1 to F8 and got a
syntax error message. I pastet it as follows:
Sub saveas()
ThisWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Owner\My Documents\" & Intro.Range("F8") &
".xls"
End Sub
 
A

Alan

Try this, there are four lines of code here, the third line that starts
with "C:\Documents..... etc and ends with & ".xls" is all one line, I think
the text is getting wrapped in the e-mail, you need to edit it back to one
line only.

You can't use Intro.Range("F8"), it needs to be Sheets("Intro").Range("F8")
if you want to refer to the name you've given the sheet.
Where I put Sheet1.Range("A1"), that refers to the name of the sheet within
the VB editor, you'll see Sheet1 (Intro) or whatever sheet it is on the top
left of the window. By referring to Sheet1. it will always refer to that
sheet even if you rename that sheet to something else. I always use this
method because unlike a formula in a worksheet, the code wont automatically
change if you rename the sheet as a formula will and its a bit tedious to
have to alter the code if there's a lot of it,
Regards,
Alan.

Sub Saveas ()
ThisWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Owner\My Documents\" &
Sheets("Intro").Range("F8") & ".xls"
End Sub
 
Top