+ Reply to Thread
Results 1 to 12 of 12

Rename worksheet tab based on cell value

  1. #1
    Registered User
    Join Date
    09-02-2010
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    32

    Rename worksheet tab based on cell value

    I have a workbook that is a yearly time sheet. Every week is a separate tab. The tab names are the dates of every Friday of the year. In cell B2 in the first worksheet contains the date of the first Friday in January, 2014. That same cell on the next sheet is linked to the first cell +7. The third sheet is linked to the first tab +14, etc....

    I set it up so that I only have to change the date of the first Friday in January and the rest of the sheets update their dates accordingly so in 2015 all the dates change in a flash. However, each worksheet's tab is also the date in B2, but not linked. What I want to do is also have the sheet tabs names changed based on the value of B2 on each sheet so I don't have to rename them manually.

    Is there an easy way to do this with VBA?

  2. #2
    Forum Contributor prabhuduraraj09's Avatar
    Join Date
    05-19-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    327

    Re: Rename worksheet tab based on cell value

    Hi Tritekfire,

    Try the below,

    HTML Code: 
    Sub Rename()
         For Each sht In ThisWorkbook.Worksheets
                sht.Name = sht.Range("b2")
         Next sht
    End Sub
    *Add reputation if my post helps and mark the thread as solved *

  3. #3
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Rename worksheet tab based on cell value

    Just a note, certain characters can't be used in sheet names. One of these characters is "/" which is common in date formats. If your date utilizes "/" you are going to need to modify the date (usually using "-" instead of "/")

  4. #4
    Registered User
    Join Date
    09-02-2010
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Rename worksheet tab based on cell value

    Which module to do place this? Do I copy it to each sheet?

  5. #5
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Rename worksheet tab based on cell value

    Place it under module 1. You don't need to copy it to any sheet. It is an independent subroutine which will access each sheet accordingly. Please note my post above regarding characters.

  6. #6
    Registered User
    Join Date
    09-02-2010
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Rename worksheet tab based on cell value

    I see what you mean about about the "/" character. The autodater works by increasing the date by 7 days on each sheet. Even though I formatted the cell to display "3-Jan", in the formula bar it displays 01/03/14. I don't have any other option like formatting the cell as text because on each sheet are the days of the week with the dates, and that feeds from that same cell B2. So I have to keep the date formatting. Does this mean I am hosed or is there another clever work around?

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    19,179

    Re: Rename worksheet tab based on cell value

    What about using a remote cell off the focus of the spreadsheet and put this formula in the cell

    =text(B2,"mm-dd-yy") and then use that cell in your macro shown above.

  8. #8
    Registered User
    Join Date
    06-25-2013
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: Rename worksheet tab based on cell value

    Quote Originally Posted by Tritekfire View Post
    I see what you mean about about the "/" character. The autodater works by increasing the date by 7 days on each sheet. Even though I formatted the cell to display "3-Jan", in the formula bar it displays 01/03/14. I don't have any other option like formatting the cell as text because on each sheet are the days of the week with the dates, and that feeds from that same cell B2. So I have to keep the date formatting. Does this mean I am hosed or is there another clever work around?
    Based on this working in the immediate pane: ? WorksheetFunction.Text(cells(11,4),"mm-dd-yyyy")
    I believe solution is to change sht.Range("b2") to
    WorksheetFunction.Text(sht.Range("b2"),"mm-dd-yyyy")

    I figured there was a VBA version of this, and I tried format and it worked:

    ? Format(cells(11,4),"mm-dd-yyyy")
    (course you could use yy or however you want to format it.)
    Last edited by klvaughnsd; 05-08-2014 at 03:23 PM.

  9. #9
    Registered User
    Join Date
    09-02-2010
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Rename worksheet tab based on cell value

    when I ran the vba I get a message that I cannot rename sheets to the same name. My very first sheet is a pivot table that combines all of the pivot tables on each sheet for each week to give a yearly summary. The name of this sheet is "Summary". When I run the macro it changes the name of the sheet to "01-00 1900". I assume it was trying to rename all of the sheets to this same thing, which means it's not working. Any ideas?

  10. #10
    Registered User
    Join Date
    06-25-2013
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: Rename worksheet tab based on cell value

    After re-reading through these posts, I can see that offered solution wouldn't work. However, I for one am confused by the vagueness of the situation. Perhaps you should list each of the current tab names and restate what they should be, apparently based on what is in cell B2.

    A first guess is the sheet names will need to be tested first to see if they are in the format of a date. And apparently some sheet names will need to be the value of B2 +7, then +14, then + 21, etc?

  11. #11
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Rename worksheet tab based on cell value

    Try this:

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    09-02-2010
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Rename worksheet tab based on cell value

    That worked like a charm stnkynts!!! I actually changed the date to just "mm-dd", and it looks just like I had it. All I have to do is change the date of B2 on the first sheet, and it does the rest for all sheets. Thanks for all the help!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Rename Multiple worksheet based on what is contained in one cell in that sheet.
    By pjmc84 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-16-2013, 11:20 AM
  2. Copy worksheet and rename based on cell range
    By jpruffle in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-08-2011, 06:53 AM
  3. Automated worksheet copy, rename based on cell results
    By humboldtguy in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-01-2009, 01:11 AM
  4. Rename worksheet based on content in Cell C3
    By jieyi in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-09-2009, 12:37 PM
  5. VBA to rename worksheet based on cell reference on another worksheet
    By Sandi99 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-20-2008, 01:46 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1