Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



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

Thread: Name Worksheet Tabs Automatically Based On Cell

  1. #1
    Join Date
    9th September 2004
    Posts
    14

    Name Worksheet Tabs Automatically Based On Cell

    Seeking to name 40 worksheets in a workbook automatically, based on Name in B1 in each worksheet. Data and worksheet names will change monthly.

    I am new to this so I will need explicit instructions. Ty

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718
    Hi BOONWEEBO

    The code below will name each Worksheet in the Workbook based on the content of cell B1 of each Worksheet each time the Workbook is opened. To use the code, right click on the Excel icon, top left next to File and select View Code. In here paste the code below.
    VB:
    Private Sub Workbook_Open() 
        Dim wSheet As Worksheet 
        On Error Resume Next 
        For Each wSheet In Me.Worksheets 
            wSheet.Name = wSheet.Range("B1") 
        Next wSheet 
        On Error Goto 0 
    End Sub 
    
    
    If certain Worksheets should be exclude let me know their names (or their Index number) and I will modify the code. There is also no error check to ensure cell B1 does not house characters that cannot be used, let me know if you would like that added.

  3. #3
    Join Date
    9th September 2004
    Posts
    14
    TY works well on the initial data. However when the data changes, and where is no reference in B1, one tab name changes to "0" and the others remain named after the previous data.

    ie. 40 sheets all properly named with first data
    The new data has only 35 entries in B1.
    These new names populate correctly,
    Sheet 36 name is changed to "0"
    The remaining sheets retain the original data names.

    How do I then rename these last 4 sheets? Number or Alpha progression is fine. TY K

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    9th September 2004
    Posts
    14
    #3 21 Hours Ago
    BOONWEEBO
    Junior Member MS Office Version: 2003
    Op System: Windows XP
    Assumed Experience: Average


    Join Date: 8th September 2004
    Posts: 2

    TY works well on the initial data. However when the data changes, and where is no reference in B1, one tab name changes to "0" and the others remain named after the previous data.

    ie. 40 sheets all properly named with first data
    The new data has only 35 entries in B1.
    These new names populate correctly,
    Sheet 36 name is changed to "0"
    The remaining sheets retain the original data names.

    How do I then rename these last 4 sheets? Number or Alpha progression is fine. TY K

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718
    How do I then rename these last 4 sheets? Number or Alpha progression is fine
    What do the other sheets get named like? I need to know this if you would like Number or Alpha progression.

  6. #6
    Join Date
    9th September 2004
    Posts
    14
    Ideally there would be no names in B1, but it is my understanding that tabs need to have text in them. If the cell is blank or "0" the tab names do not change. TY again for your help

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    4,032
    Ty,

    I've added to Dave's code. Check out the following. If the range B1 is blank then it renames the sheet with "Sheet" and it index number.

    VB:
    Private Sub Workbook_Open() 
        Dim wSheet As Worksheet 
        On Error Resume Next 
        For Each wSheet In Me.Worksheets 
            If wSheet.Range("B1") = "" Then 
                wSheet.Name = "Sheet" & wSheet.Index 
            Else 
                wSheet.Name = Format(wSheet.Range("B1"), "mmm dd, yyyy") 
            End If 
        Next wSheet 
        On Error Goto 0 
    End Sub 
    
    
    Regards,
    Barry

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    9th September 2004
    Posts
    14
    That does the trick! I have run it changing the B1 data several times and everything renames properly now.

    TY Barry and Dave

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    2nd August 2010
    Posts
    1

    Re: Name Worksheet Tabs Automatically Based On Cell

    Thanks for the tips, this is great and works well - however, how do you omit specific Worksheets? Thanks very much for the help, I appreciate this is an old post but I have just recently had the need to do this and need to know how specific sheets can be omitted from the code.

    Many thanks

    Alex

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    4,032

    Re: Name Worksheet Tabs Automatically Based On Cell

    What is the criteria for omitting sheets? Is it the name? Is it the position?

    If it's the name of the worksheet then

    VB:
    Private Sub Workbook_Open() 
        Dim wSheet As Worksheet 
        On Error Resume Next 
        For Each wSheet In Me.Worksheets 
            [COLOR="red"]if wSheet.Name = "SomeName" then[/COLOR] 
            If wSheet.Range("B1") = "" Then 
                wSheet.Name = "Sheet" & wSheet.Index 
            Else 
                wSheet.Name = Format(wSheet.Range("B1"), "mmm dd, yyyy") 
            End If 
            [COLOR="red"]End If[/COLOR] 
        Next wSheet 
        On Error Goto 0 
    End Sub 
    
    
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on


    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Rename/Name Sheet Tabs Based on a Cell
    By ilpap75 in forum EXCEL HELP
    Replies: 5
    Last Post: February 24th, 2012, 06:30
  2. Replies: 3
    Last Post: February 19th, 2009, 02:55
  3. Replies: 6
    Last Post: May 16th, 2008, 06:31
  4. Replies: 8
    Last Post: April 17th, 2008, 12:29
  5. Replies: 1
    Last Post: January 28th, 2008, 11:21

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
  •  
porno