Automatic File Name based on cell content

[Prangk]

I just want to expedite my "save as" procedure through this question.
would like to have an automatic file name based on cell (A1) content
Then it will save the file "automatically" on a folder located at th
desktop (folder name "Current List"). But all of these will be o
"save as" command. Is this possible?

I will appreciate any help

 

[Bob Phillips]

On XP all users have a separate desktop. Here is an example

activeworkbook.saveas "C:\Documents and Settings\Bob\Desktop\" &
activesheet.Range("A1")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Prangk]

Thanks for the reply. I copied and paste through the visual basic bu
did not work. I am sure I did it wrong. Can you please tell me how t
make it work. Thank

 

[Bob Phillips]

What do you have in cell A1?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Craig]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim CellName As String
CellName = Range("A1")
ActiveWorkbook.SaveAs Filename:="C:\{Path leading to
Desktop}\Current List\" & CellName & ".xls"
End Sub

 

[Prangk]

I think I am almost there. The content of A1 is from C11 (text and dat
combine). I got two sheets in the workbook. Sheet 1 name is "data
sheet 2 name is "calculation". The "A1" that I need is from sheet 1
This is what I tried...


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim CellName As String
CellName = Range("A1")
ActiveWorkbook.SaveAs Filename:="C:\Document an
Settings\Owner\Desktop\comparable\" & CellName & ".xls"
End Sub

When I pressed "save as", it saves but did not use the file name that
want and it did not go to the folder that I want it to go. Which on
is wrong on the code.

Thanks agai

 

[Bob Phillips]

It looks okay, but try this version

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim CellName As String
Dim FileName As String
FileName = :="C:\Document and Settings\Owner\Desktop\comparable\"
CellName = Activeworkbook.Worksheets("Sheet1").Range("A1")
ActiveWorkbook.SaveAs FilenameFileName & CellName & ".xls"
End Sub


If that fails, insert a MsgBoc FileName & CellName & ".xls" and see what you
get.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Prangk]

I can't make it work. I just copied and pasted your code but nothin
happened. Almost giving up. Any last help before i stopped trying thi
automation problem