Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Code to get worksheet name into a cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2010
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Code to get worksheet name into a cell

    Is it possible to have the worksheet name autopopulate into a cell?

    Example - I want the sheet name to appear in cell F1. Is that possible? Also, if I rename Sheet 1 to 12345, the info in cell F1 would change from Sheet 1 to 12345.

  2. #2
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to get worksheet name into a cell

    Put this formula in F1:

    Code:
    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

  3. #3
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to get worksheet name into a cell

    Code:
    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

  4. #4
    Board Regular
    Join Date
    Mar 2010
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to get worksheet name into a cell

    Nice thanks to both of you!

  5. #5
    Board Regular
    Join Date
    Mar 2010
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to get worksheet name into a cell

    For some reason, when I copy the worksheet which is using the reference above, the new worksheet displays #VALUE in the cell, instead of the sheet name. If I save the file, it still says #VALUE . If I save, close, and reopen - the formula then works and the cell shows the worksheet name. Any way to get the sheet name to show without closing and reopening the workbook?

  6. #6
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to get worksheet name into a cell

    Do you have calculations set to manual or Automatic?

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,133
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Code to get worksheet name into a cell

    Are you copying the sheet to a brand new workbook?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to get worksheet name into a cell

    Fluff hit the nail on the head, of course. The CELL("filename", A1) finds the full path...but if you haven't saved the workbook, there is no path. If you save it, does the sheet name update correctly?

  9. #9
    Board Regular
    Join Date
    Mar 2010
    Posts
    103
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to get worksheet name into a cell

    Yes, copying to new workbook. When I click save, nothing changes. When I close the book and then reopen, the information populates in the cell.

    Any way to make it populate without closing and reopening?

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,133
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Code to get worksheet name into a cell

    After saving press F9, does that make a difference?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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