Return a sheet name

  • Hi All
    Please can anyone help.
    I am trying to paste a sheet name into a cell on a different sheet, but my sheets are named "Nov 04", Dec 04" etc, and excel keeps changing the name into a date, which returns "Nov-05"


    I have tried making a variable (Nme) as String, and I have tried changing the numberformat of the cell to "Text" and "General", but neither of these work


    The following is the part of the macro I have used
    Nme = tsheet.Name
    Range("A1") = Nme


    Please help
    Flo

  • Re: Return a sheet name


    Hi Pradeep
    I tried that, and got Nov 05 for some reason
    As soon as I put the variable Nme into cell A1 it changes it to 04/11/2005 even if I make Nme a string
    Thanks
    Flo

  • Re: Return a sheet name


    Excel is interpreting your sheet name as a partial date (but I guess you know that by now)


    Nov 04 equates to 4th Nov - you get Nov 05 as Excel assumes that it's thie current year as it din't find a year only a day & month


    You can use a quotation mark to force text like so


    Code
    [a1] = "'" & Activesheet.name


    Where the sheet name = Nov 04

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!