Hi Everyone,

I have the below routine set to copy a page to my desktop. Is there a way
to have it change the save name to a particular cell's value? If Range(€œL6€�)
equals a certain word, I would like it to save as that particular name.

Thanks in advance for your thoughts - Roger

Sub Finalstop()

ActiveSheet.Copy
ChDir "C:\Documents and Settings\Roger\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Roger\Desktop\New File.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close

End

hi
this might work...
Dim mypath As String
Dim nrng As Range
Dim fname As String
Set nrng = Range("A1")
mypath = "C:\your\file\path\"
fname = nrng.Value & ".xls"
ActiveWorkbook.SaveAs Filename:= _
mypath & fname, FileFormat:= _
xlNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

regards
FSt1

"Roger" wrote:

Hi Everyone,

I have the below routine set to copy a page to my desktop. Is there a way
to have it change the save name to a particular cell's value? If Range(€œL6€�)
equals a certain word, I would like it to save as that particular name.

Thanks in advance for your thoughts - Roger

Sub Finalstop()

ActiveSheet.Copy
ChDir "C:\Documents and Settings\Roger\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Roger\Desktop\New File.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close

End

Your idea worked great.

Thanks so much for all your help.

Roger


"FSt1" wrote:

hi
this might work...
Dim mypath As String
Dim nrng As Range
Dim fname As String
Set nrng = Range("A1")
mypath = "C:\your\file\path\"
fname = nrng.Value & ".xls"
ActiveWorkbook.SaveAs Filename:= _
mypath & fname, FileFormat:= _
xlNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

regards
FSt1

"Roger" wrote:

Hi Everyone,

I have the below routine set to copy a page to my desktop. Is there a way
to have it change the save name to a particular cell's value? If Range(€œL6€�)
equals a certain word, I would like it to save as that particular name.

Thanks in advance for your thoughts - Roger

Sub Finalstop()

ActiveSheet.Copy
ChDir "C:\Documents and Settings\Roger\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Roger\Desktop\New File.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close

End