Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Worksheet name without slashes (Excel XP)

    I am writing a macro and I need to name the worksheet by a range I have formatted as a date, 10/20/2002. It won't accept the slashes in the name of the worksheet so I'd like to make it 10-20-2002. How can I write code to do that. Thank you

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet name without slashes (Excel XP)

    This works from Excel2000 and up:

    sFileName=Replace(sFilename,"/","-")

    Excel 97:

    sFileName=Application.WorksheetFunction.Substitute (sFilename,"/","-")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet name without slashes (Excel XP)

    Another solution from the one already posted is a macro:
    <pre>Public Sub NameSheet(dtName As String)
    Dim ary

    ary = Split(dtName, "/")
    If ary(0) = dtName Then
    MsgBox "Date provided has invalid format, missing '/'"
    Else
    ActiveSheet.Name = ary(0) & "-" & ary(1) & "-" & ary(2)
    End If
    End Sub
    </pre>

    Usage: NameSheet("10/30/2002")

    This won't work in xl97 since it doesn't have Split but you can write your own easily. I think the Replace solution is better but always nice to have a choice.

    Deb <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>

Posting Permissions

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