+ Reply to Thread
Results 1 to 7 of 7

Automatically change sheet (tab) names based on cell on sheet1

  1. #1
    Registered User
    Join Date
    03-18-2015
    Location
    Detroit
    MS-Off Ver
    2013
    Posts
    78

    Question Automatically change sheet (tab) names based on cell on sheet1

    I am creating a workbook with 40 tabs. The first tab is "jobs". It lists jobs starting in cell B5 and goes down to B44.

    I am pre-creating 40 sheets, they will ALL pull information from the FIRST job sheet to fill in certain areas. That Part I have no problem with.
    I just want the sheets names to change automatically if something is entered into the corresponding cell on the first sheet.
    The cells on the job sheet will be empty until something is entered.

    Sheet2 should be named whatever is entered into cell B6 on the job sheet (sheet1)
    Sheet3 should be named whatever is entered into cell B7 on the job sheet (sheet1)
    Sheet4 should be named whatever is entered into cell B8 on the job sheet (sheet1)
    and so on...

    And it would need to happen automatically without having to push a macro button.

    I can modify whatever code I need on each of the 40 pre-made sheets no problem.

    I shouldn't have any issues with people entering illegal characters or anything like that.

    Thanks for any ideas!

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Automatically change sheet (tab) names based on cell on sheet1

    If the sheets are already there, put this code in the sheet1 module:

    Please Login or Register  to view this content.
    To put the sheets in - from a three sheet(!) book:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    03-18-2015
    Location
    Detroit
    MS-Off Ver
    2013
    Posts
    78

    Re: Automatically change sheet (tab) names based on cell on sheet1

    xladept

    First of all thanks a lot!
    I can make that work. only difference is your pulls from the same sheet the sheet is going to be named from. My ideas was to have every sheet pull from the first sheet (job sheet). But since that job number is on every sheet anyway it should work, I can just change the reference cell because these sheets are different from the job sheet, so the job number is in a different spot.

    The only issue I am having is if the reference cells contents are deleted after something was entered it throws up an error that will probably freak everyone out (runtime error).

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Automatically change sheet (tab) names based on cell on sheet1

    A small change:

    Please Login or Register  to view this content.
    *This code only goes in Sheet1 - did you put it everywhere?

  5. #5
    Registered User
    Join Date
    03-18-2015
    Location
    Detroit
    MS-Off Ver
    2013
    Posts
    78

    Re: Automatically change sheet (tab) names based on cell on sheet1

    Issue fixed, it was my fault, code works good.
    Last edited by PitBoo; 09-29-2017 at 02:41 PM.

  6. #6
    Registered User
    Join Date
    03-18-2015
    Location
    Detroit
    MS-Off Ver
    2013
    Posts
    78

    Re: Automatically change sheet (tab) names based on cell on sheet1

    Sorry I was putting it in the wrong sheet, I thought it went in the other sheets and not the first one.

    Works great now!

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Automatically change sheet (tab) names based on cell on sheet1

    Excellent! Thanks for the rep!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Replies: 4
    Last Post: 10-11-2016, 06:45 PM
  2. Automatically Change Sheet Names Based On Cell Values - merged cells
    By Elizabethz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-24-2014, 11:35 AM
  3. change cell names based on new sheet name
    By tdebb8594 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-03-2014, 02:25 PM
  4. Replies: 7
    Last Post: 11-25-2012, 01:24 AM
  5. automatically change cell value based on the value in other sheet.
    By star.dust9212 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 11-15-2011, 06:21 AM
  6. Replies: 0
    Last Post: 09-24-2010, 01:05 PM
  7. Automatically change Sheet names by typing in a cell
    By BLRITCHIE in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-23-2010, 04:19 AM

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