Consulting

Results 1 to 6 of 6

Thread: Solved: Rename sheet using NOW function ???

  1. #1
    VBAX Contributor
    Joined
    Jul 2009
    Posts
    157
    Location

    Solved: Rename sheet using NOW function ???

    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 ?

    [VBA]
    Dim NewName As Integer
    NewName.Value = CDbl(Now)
    Worksheets.Add
    ActiveSheet.Name = NewName
    [/VBA]


    thanks !

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Try prefixing the name with an underscore, "_".

  3. #3
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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.

    [VBA]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[/VBA]
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  4. #4
    VBAX Contributor
    Joined
    Jul 2009
    Posts
    157
    Location
    Thanks Lucas, that worked as I had hoped !! I appreciate it :-)

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You don't need two variables Steve

    [vba]
    NewName = Application.Text(Now, "mmmm dd yyyy h-mm AM/PM")
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    I didn't even think of that Bob. Thanks.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •