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

  • 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

  • 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

    [a1] = "'" &

    Where the sheet name = Nov 04

Participate now!

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