Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Name Worksheet Tabs Automatically Based On Cell

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.
    Code:
    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.

    Comment


    • #3
      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

      Comment


      • #4
        #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

        Comment


        • #5
          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.

          Comment


          • #6
            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

            Comment


            • #7
              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.

              Code:
              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
              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!

              Comment


              • #8
                That does the trick! I have run it changing the B1 data several times and everything renames properly now.

                TY Barry and Dave

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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

                    Code:
                    Private Sub Workbook_Open()
                        Dim wSheet As Worksheet
                        On Error Resume Next
                        For Each wSheet In Me.Worksheets
                            if wSheet.Name = "SomeName" then
                                If wSheet.Range("B1") = "" Then
                                    wSheet.Name = "Sheet" & wSheet.Index
                                Else
                                    wSheet.Name = Format(wSheet.Range("B1"), "mmm dd, yyyy")
                                End If
                            End If
                        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!

                    Comment


                    • #11
                      Re: Name Worksheet Tabs Automatically Based On Cell

                      Hello Alex,

                      Welcome to Ozgrid. While we are glad to have you as part of the community, please take the time to read the forum rules.

                      Posting your questions in threads started by others is a violation of the forum rules and is known as thread hijacking.
                      ALWAYS start a new thread for your questions.

                      If you need further help, start a new thread and include a link back to this thread.
                      AAE
                      ----------------------------------------------------

                      Forum Rules | Message to Cross Posters | How to use Tags

                      Comment

                      Trending

                      Collapse

                      There are no results that meet this criteria.

                      Working...
                      X