Add a worksheet with a name as a date

  • Thread starter Thread starter John G
  • Start date Start date
J

John G

Hi all,

I use today() or now() to enter the current date into a cell. Then
today() is removed using Selection.PasteSpecial Paste:=xlPasteValues
leaving just the date.

I am now left with a date in the following format 15/10/03. I can
alter the format of this eg to 15th October 2003 but my problem is
that the underlying format remains the same.

This is a problem because when I create a worksheet using this date
(Sheets.Add.Name) I can not name a worksheet with the character / . I
know I can change my regional settings separator to something
different but then this will affect all my other excel programs.

I need to enter the system date (current) automatically so I can not
manually add as text the date.

Any thoughts would be appreciated.

Thanks,

John G
 
Not totally sure what you're after, but have you
investigated the format function?

try this in the immediate window :

?now()

and then

?format(now(),"dd mmm yyyy")

you could then add a new worksheet like this :

set wkshtTemp=activeworkbook.Worksheets.Add
wkshtTemp.name="Sheet " & format(now(),"dd mmm yyyy")

anyway, I'm sure you can experiment

Regards

Rog
 
Back
Top