Hi All,
I use the below to save a copy of my worksheet to my desktop before its
contents are cleared. I would like to add a now() or today() date stamp to
the fName. Any ideas how to do that?

I’ve combined the ranges on my excel spreadsheet cell H5 with a text date
style =H5& " "&TEXT(TODAY(),"mm/dd/yy"). When I try to add this to the
macro below, I get this error –
Run-Time error 1004.

Any thoughts on how I can have the output file contain my H5 value which is
text and today’s date?

Thank you – Jenny B.


Sub Save()

Dim myPath As String
Dim nRng As Range
Dim fName As String
Set nRng = Range("H5")

ActiveSheet.Copy

Call DeleteAllCode
ActiveSheet.Shapes("Send").Visible = False
myPath = "C:\Documents and Settings\Name Here\Desktop\"
fName = nRng.Value & ".xls"

ActiveWorkbook.SaveAs filename:= _
myPath & fName, FileFormat:= _
xlNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Call TransfertoLog
Call CLEAR
ActiveSheet.Visible = False

End Sub

hi
wild guessing here.
slashes are forbidden as a file naming conviction. no slashes in a file name.
try this....
style =H5& " "&TEXT(TODAY(),"mmddyy")
your date will end up looking like this......062108.
i use this style when i'm adding a date to a file name. mainly because
slashes are forbidden.

regards
FSt1

"Jenny B." wrote:

> Hi All,
> I use the below to save a copy of my worksheet to my desktop before its
> contents are cleared. I would like to add a now() or today() date stamp to
> the fName. Any ideas how to do that?
>
> I’ve combined the ranges on my excel spreadsheet cell H5 with a text date
> style =H5& " "&TEXT(TODAY(),"mm/dd/yy"). When I try to add this to the
> macro below, I get this error –
> Run-Time error 1004.
>
> Any thoughts on how I can have the output file contain my H5 value which is
> text and today’s date?
>
> Thank you – Jenny B.
>
>
> Sub Save()
>
> Dim myPath As String
> Dim nRng As Range
> Dim fName As String
> Set nRng = Range("H5")
>
> ActiveSheet.Copy
>
> Call DeleteAllCode
> ActiveSheet.Shapes("Send").Visible = False
> myPath = "C:\Documents and Settings\Name Here\Desktop\"
> fName = nRng.Value & ".xls"
>
> ActiveWorkbook.SaveAs filename:= _
> myPath & fName, FileFormat:= _
> xlNormal, Password:="", _
> WriteResPassword:="", _
> ReadOnlyRecommended:=False, _
> CreateBackup:=False
> ActiveWorkbook.Close
> Call TransfertoLog
> Call CLEAR
> ActiveSheet.Visible = False
>
> End Sub
>

Thank your for you help. You were right on the money and it now works great.
I should have known better and remembered that the slashes represent a path
and that’s why I was getting that annoying error.

Thanks again – Jenny B.


"FSt1" wrote:

> hi
> wild guessing here.
> slashes are forbidden as a file naming conviction. no slashes in a file name.
> try this....
> style =H5& " "&TEXT(TODAY(),"mmddyy")
> your date will end up looking like this......062108.
> i use this style when i'm adding a date to a file name. mainly because
> slashes are forbidden.
>
> regards
> FSt1
>
> "Jenny B." wrote:
>
> > Hi All,
> > I use the below to save a copy of my worksheet to my desktop before its
> > contents are cleared. I would like to add a now() or today() date stamp to
> > the fName. Any ideas how to do that?
> >
> > I’ve combined the ranges on my excel spreadsheet cell H5 with a text date
> > style =H5& " "&TEXT(TODAY(),"mm/dd/yy"). When I try to add this to the
> > macro below, I get this error –
> > Run-Time error 1004.
> >
> > Any thoughts on how I can have the output file contain my H5 value which is
> > text and today’s date?
> >
> > Thank you – Jenny B.
> >
> >
> > Sub Save()
> >
> > Dim myPath As String
> > Dim nRng As Range
> > Dim fName As String
> > Set nRng = Range("H5")
> >
> > ActiveSheet.Copy
> >
> > Call DeleteAllCode
> > ActiveSheet.Shapes("Send").Visible = False
> > myPath = "C:\Documents and Settings\Name Here\Desktop\"
> > fName = nRng.Value & ".xls"
> >
> > ActiveWorkbook.SaveAs filename:= _
> > myPath & fName, FileFormat:= _
> > xlNormal, Password:="", _
> > WriteResPassword:="", _
> > ReadOnlyRecommended:=False, _
> > CreateBackup:=False
> > ActiveWorkbook.Close
> > Call TransfertoLog
> > Call CLEAR
> > ActiveSheet.Visible = False
> >
> > End Sub
> >

As well, if you format your date as yymmdd, (or yyyy mm dd as I do) then the
files will sort in the right time order....

OM

"Jenny B." <(E-Mail Removed)> wrote in message
news:57F0D66C-570E-4C83-AC7E-(E-Mail Removed)...
> Thank your for you help. You were right on the money and it now works
> great.
> I should have known better and remembered that the slashes represent a
> path
> and that's why I was getting that annoying error.
>
> Thanks again - Jenny B.
>
>
> "FSt1" wrote:
>
>> hi
>> wild guessing here.
>> slashes are forbidden as a file naming conviction. no slashes in a file
>> name.
>> try this....
>> style =H5& " "&TEXT(TODAY(),"mmddyy")
>> your date will end up looking like this......062108.
>> i use this style when i'm adding a date to a file name. mainly because
>> slashes are forbidden.
>>
>> regards
>> FSt1
>>
>> "Jenny B." wrote:
>>
>> > Hi All,
>> > I use the below to save a copy of my worksheet to my desktop before its
>> > contents are cleared. I would like to add a now() or today() date
>> > stamp to
>> > the fName. Any ideas how to do that?
>> >
>> > I've combined the ranges on my excel spreadsheet cell H5 with a text
>> > date
>> > style =H5& " "&TEXT(TODAY(),"mm/dd/yy"). When I try to add this to
>> > the
>> > macro below, I get this error -
>> > Run-Time error 1004.
>> >
>> > Any thoughts on how I can have the output file contain my H5 value
>> > which is
>> > text and today's date?
>> >
>> > Thank you - Jenny B.
>> >
>> >
>> > Sub Save()
>> >
>> > Dim myPath As String
>> > Dim nRng As Range
>> > Dim fName As String
>> > Set nRng = Range("H5")
>> >
>> > ActiveSheet.Copy
>> >
>> > Call DeleteAllCode
>> > ActiveSheet.Shapes("Send").Visible = False
>> > myPath = "C:\Documents and Settings\Name Here\Desktop\"
>> > fName = nRng.Value & ".xls"
>> >
>> > ActiveWorkbook.SaveAs filename:= _
>> > myPath & fName, FileFormat:= _
>> > xlNormal, Password:="", _
>> > WriteResPassword:="", _
>> > ReadOnlyRecommended:=False, _
>> > CreateBackup:=False
>> > ActiveWorkbook.Close
>> > Call TransfertoLog
>> > Call CLEAR
>> > ActiveSheet.Visible = False
>> >
>> > End Sub
>> >