I'm trying to do a save with a variable filename. I read this might be
easier if I set a certain cell to equal the filename that I am looking
for and then Dim that as a variable in the code and call on the cell
for the name.

How do I set a cell to equal something like
NS Tues Aug 1st

I wanted the filename to be something like that, the formatting can
change if its easier a different way, but I need "NS" the day of the
week, then the month and date. The cell also has to have a "Date-1" in
there since I need it to equal the date from the day before.

I want to save this file variably and was going to do something like
this

Dim filename as string

filename = Range("A1")
ChDir "D:\My Documents\Temp\"


ActiveWorkbook.SaveAs filename:= _
"D:\My Documents\Temp\" & filename, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _



Thanks,

~J


--
nbaj2k
------------------------------------------------------------------------
nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
View this thread: http://www.excelforum.com/showthread...hreadid=567397

You don't need a cell (and it isn't easier in my opinion)

Dim filename as string, s as String

s = "th"
if Day(date-1) <= 10 or Day(date-1) 19 then
Select Case Right(Day(Date-1),1) * 1
Case 1
s = "st"
Case 2
s = "nd"
Case 3
s = "rd"
Case Else
s = "th"
end Select
End if
filename = "NS " & format(Date-1,"ddd mmm d") & s & ".xls"
ChDir "D:\My Documents\Temp\"


ActiveWorkbook.SaveAs filename:= _
"D:\My Documents\Temp\" & filename, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _

--
Regards,
Tom Ogilvy


"nbaj2k" wrote:


I'm trying to do a save with a variable filename. I read this might be
easier if I set a certain cell to equal the filename that I am looking
for and then Dim that as a variable in the code and call on the cell
for the name.

How do I set a cell to equal something like
NS Tues Aug 1st

I wanted the filename to be something like that, the formatting can
change if its easier a different way, but I need "NS" the day of the
week, then the month and date. The cell also has to have a "Date-1" in
there since I need it to equal the date from the day before.

I want to save this file variably and was going to do something like
this

Dim filename as string

filename = Range("A1")
ChDir "D:\My Documents\Temp\"


ActiveWorkbook.SaveAs filename:= _
"D:\My Documents\Temp\" & filename, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _



Thanks,

~J


--
nbaj2k
------------------------------------------------------------------------
nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
View this thread: http://www.excelforum.com/showthread...hreadid=567397