PDA

View Full Version : Solved: Rename sheet using NOW function ???



bdsii
03-18-2010, 07:42 AM
I would like to add a new worksheet and want the name of the new worksheet to be the date and time. This will help if the macro is run repeatedly so that each time it is run, the worksheet name will be unique.

I tried the code below and it did not work. I change the type of NewName to String and that didn't help either.

Any ideas ?


Dim NewName As Integer
NewName.Value = CDbl(Now)
Worksheets.Add
ActiveSheet.Name = NewName



thanks !

SamT
03-18-2010, 08:07 AM
Try prefixing the name with an underscore, "_".

lucas
03-18-2010, 08:08 AM
You diminsioned the variable as a number when it's really a string.

What you want will give you dateserial and timeserial which may not be what you are looking for.

You can format them but you can't have : or / etc.

Dim NewName1 As String
Dim newname2 As String
NewName1 = Format(Now, "mmmm dd yyyy")
newname2 = Format(Now, "h-mm AM/PM")
Worksheets.Add
ActiveSheet.Name = NewName1 & " " & newname2

bdsii
03-18-2010, 09:15 AM
Thanks Lucas, that worked as I had hoped !! I appreciate it :-)

Bob Phillips
03-18-2010, 10:18 AM
You don't need two variables Steve


NewName = Application.Text(Now, "mmmm dd yyyy h-mm AM/PM")

lucas
03-18-2010, 10:26 AM
I didn't even think of that Bob. Thanks.