This doesn't sound like a good idea to me. I'd create a dedicated macro to do
the save and only run it when I needed to--not for each change to A1.

But if you want...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
dim myFolderName as string
dim myFileName as string

if target.cells.count 1 then
exit sub 'one cell at a time!
end if

if intersect(target, me.range("a1:B1")) is nothing then
exit sub
end if

myfilename = me.range("a1").value
if myfilename = "" then
exit sub 'no file entered
end if

myfoldername = me.range("b1").value
if myfoldername = "" then
exit sub 'no folder entered
end if

if right(myfoldername,1) < "\" then
myfoldername = myfoldername & "\"
end if

application.displayalerts = false 'no overwrite this file pompt
on error resume next
thisworkbook.saveas filename:=myfoldername & myfilename, _
fileformat:=xlworkbooknormal
if err.number < 0 then
err.clear
msgbox "Error while saving"
else
msgbox "saved ok" 'comment out when done testing!
end if
on error goto 0
application.displayalerts = true

End sub

I really wouldn't use this.


TJ wrote:

Yes, I would & the folder to be picked from the next cell b1
thanks

"Dave Peterson" wrote:

The only way to rename the file/workbook is to save it with a new name.

Are you sure you want do this?

If so, what folder would you save it to--always the same or pick up the folder
in another cell?

TJ wrote:

Please advise where I would insert this code in the sheet or the workbook.
I am looking for soemthing on the similar lines of sheet i.e.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < Range("a1").Address Then Exit Sub
ActiveSheet.Name = Target

where I am able to change the file name from the sheet itself

thanks
TJ

"Gary''s Student" wrote:

The usual way to rename an excel file from within Excel is to SaveAs the new
name:

Sub Macro2()
Sheets("Sheet1").Activate
ActiveWorkbook.SaveAs Filename:=Range("A1").Value
End Sub

--
Gary''s Student - gsnu200850


"TJ" wrote:

Hi ,

Sorry my question was that I would like to rename the excel file to say
whatever maybe typed in cell A1

regards
TJ

--

Dave Peterson


--

Dave Peterson