+ Reply to Thread
Results 1 to 13 of 13

Using VBA to rename worksheet based on cell value

  1. #1
    Registered User
    Join Date
    06-15-2016
    Location
    California, USA
    MS-Off Ver
    Office 365
    Posts
    5

    Question Using VBA to rename worksheet based on cell value

    Here is my situation: I have the code for changing the worksheet name based on a cell on that worksheet. The cell that determines the worksheet name gets its contents from a cell in a different, "index" worksheet. When I edit the cell in the index worksheet, the corresponding worksheet's name doesn't change unless I double-click in the name determining cell and press enter. Is there a way to have this "refresh" automatically when I edit the other "index" worksheet?

    Here is the code I'm using:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B12")) Is Nothing Then
    ActiveSheet.Name = ActiveSheet.Range("B12")
    End If
    End Sub


    (Note: I copied this code from another site. I am not very well versed in VBA.)
    Last edited by maciec; 06-17-2016 at 11:38 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    862

    Re: Using VBA to rename worksheet based on cell value

    Hello Maciec,

    A simple formula and a slight adjustment to your code may be all you need. But first, for the sake of the exercise, lets make some assumptions:-

    1) Cell A1 in the "Index" sheet (I'll refer to it as Sheet2) is the cell where you make the name changes.
    2) Cell B12 in Sheet1 is the cell you have allocated for the code to reference for the sheet name changes.
    3) The Active Sheet is Sheet2.

    In Sheet1 B12, type the following formula:-

    Please Login or Register  to view this content.
    Don't use cell B12 for any other purpose as typing anything else in it will delete the formula.

    In the Sheet2 module, place the following code:-


    Please Login or Register  to view this content.
    You'll probably notice the minor changes to your code and you'll notice that we are referencing Sheet1 from Sheet2.

    To implement the code, right click on the Sheet2 ("Index") tab and select "view code" from the menu that appears. In the big white field that then appears, paste the above code.

    Now, every time that you change the name in cell A1 Sheet2, the formula in Sheet1 B12 will take that name across to Sheet1 B12 and, from there, the code will do the rest.

    In a situation as yours, it would be best to refer to each sheet by its sheet code in any VBA code as I have done above. In the Project Explorer frame to the left of the main code field, you'll see all your sheets listed with the sheet code (Sheet1, Sheet2..etc.) with the given sheet name beside it. This is a very secure way of coding so as sheet names may change, the reference won't and you won't have to adjust the code each time a sheet name changes.

    I've attached my test work book for you to play with.

    I hope that this helps.

    Cheerio,
    vcoolio.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-15-2016
    Location
    California, USA
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Using VBA to rename worksheet based on cell value

    Thanks vcoolio! I will give this a try and let you know if it works for me.

  4. #4
    Registered User
    Join Date
    06-15-2016
    Location
    California, USA
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Using VBA to rename worksheet based on cell value

    It works, but only for one sheet. I have 20 other sheets that I need this to work for. How can I do this?

  5. #5
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    862

    Re: Using VBA to rename worksheet based on cell value

    Hello Maciec,

    Aha. Many sheets!

    A slight change of plan then.

    In your "Index" sheet, I'll assume that Column A, not just cell A1, will be used for the name changing purposes. As you need to transfer those names as required to cell B12 of each sheet, then use the same formula in B12 for each sheet but referencing consecutive cells in Column A of the "Index" sheet.
    So, for Sheet1, the formula in B12 should be :=

    =Index!A1

    for Sheet3 (Sheet2 is the "Index" sheet):-

    =Index!A2

    for Sheet4:-

    =Index!A3

    etc., etc. etc.....

    An easier way to do this for each sheet:-

    In B12 of Sheet1, place the equals (=) symbol then go to the "Index" sheet and click on cell A1 (it will high-light with "marching ants) and hit Enter. The formula will then appear in Sheet1, B12. Do the same for Sheet3, Sheet4 etc..............

    Now, place the following code in the "Index" sheet module as previously explained:-

    Please Login or Register  to view this content.
    Thus, each time a name is changed in any cell in Column A of the "Index" sheet, the relevant tab will be re-named. I've attached an updated test work book to show you how it works. There are a few notes for you in the test work book.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-15-2016
    Location
    California, USA
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Using VBA to rename worksheet based on cell value

    Thanks again vcoolio! But I seem to be having trouble with the execution. I keep getting the Run-time error '1004'. I've gone into the settings and checked the box to allow Visual Basic Macros, but it still doesn't work. I have attached an image of the VBA window that comes up when I click on "debug." There's also a copy of the workbook I'm trying to work with.

    RunTime1004Result.png
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    862

    Re: Using VBA to rename worksheet based on cell value

    Hello Maciec,

    I see that you've changed the formula to suit your needs. Very good!

    The error shown in the screen shot is brought about by the fact that you don't have a sheet named "Index". So modify the code as follows:-


    Please Login or Register  to view this content.
    and you should be back on track. I've tested it in your work book and it now works fine.

    Good luck! Come back if anything else crops up.

    Cheerio,
    vcoolio.

  8. #8
    Registered User
    Join Date
    06-15-2016
    Location
    California, USA
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Using VBA to rename worksheet based on cell value

    I probably should have seen that. Thank you so much for your help! Everything is working beautifully!

  9. #9
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    862

    Re: Using VBA to rename worksheet based on cell value

    Hello Maciec,

    You're welcome. Glad that I was able to help.

    Cheerio,
    vcoolio.

  10. #10
    Forum Contributor
    Join Date
    03-22-2016
    Location
    Philippines
    MS-Off Ver
    MS Office 2013
    Posts
    124

    Re: Using VBA to rename worksheet based on cell value

    Quote Originally Posted by vcoolio View Post
    Hello Maciec,

    Aha. Many sheets!

    A slight change of plan then.

    In your "Index" sheet, I'll assume that Column A, not just cell A1, will be used for the name changing purposes. As you need to transfer those names as required to cell B12 of each sheet, then use the same formula in B12 for each sheet but referencing consecutive cells in Column A of the "Index" sheet.
    So, for Sheet1, the formula in B12 should be :=

    =Index!A1

    for Sheet3 (Sheet2 is the "Index" sheet):-

    =Index!A2

    for Sheet4:-

    =Index!A3

    etc., etc. etc.....

    An easier way to do this for each sheet:-

    In B12 of Sheet1, place the equals (=) symbol then go to the "Index" sheet and click on cell A1 (it will high-light with "marching ants) and hit Enter. The formula will then appear in Sheet1, B12. Do the same for Sheet3, Sheet4 etc..............

    Now, place the following code in the "Index" sheet module as previously explained:-

    Please Login or Register  to view this content.
    Thus, each time a name is changed in any cell in Column A of the "Index" sheet, the relevant tab will be re-named. I've attached an updated test work book to show you how it works. There are a few notes for you in the test work book.
    Hi Vcoolio,

    Is it possible to just name worksheets based directly on just the Index sheet not on the B12 cell inside that worksheets?

    Thanks

  11. #11
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    862

    Re: Using VBA to rename worksheet based on cell value

    Hello Dedark05,

    If you are re-naming all sheets from a list in an "Index" sheet (say Column A), you could try the following code:-


    Please Login or Register  to view this content.
    If its not quite what you would like then please start a new thread properly explaining what you would like to do. Someone should be able to help you from there.

    This thread is Maciec's thread and has been marked as solved.

    Cheerio,
    vcoolio.

  12. #12
    Forum Contributor
    Join Date
    03-22-2016
    Location
    Philippines
    MS-Off Ver
    MS Office 2013
    Posts
    124

    Re: Using VBA to rename worksheet based on cell value

    Exactly as what I need. Thanks, Vcoolio!

  13. #13
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    862

    Re: Using VBA to rename worksheet based on cell value

    Hello Dedark,

    You're welcome. Remember to start a new thread should you have any further queries regards same.

    Cheerio,
    vcoolio.

+ 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. Rename worksheet based on cell value
    By sbeirne58 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-15-2015, 12:11 PM
  2. Rename worksheet based on cell value
    By sbeirne58 in forum Excel General
    Replies: 1
    Last Post: 07-15-2015, 11:58 AM
  3. Rename worksheet based on cell with formula
    By JimmyT10 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2015, 08:44 AM
  4. [SOLVED] Rename worksheet tab based on cell value
    By Tritekfire in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-08-2014, 04:22 PM
  5. 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
  6. 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
  7. 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