Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Automatically name worksheets based on cell values from master sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2017
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Automatically name worksheets based on cell values from master sheet

    I would like to have names listed in sheet1 in column A, beginning at A3 and ending on A30.

    So if there is a name in A3 ( George) and (Sam) in A4, (John) in A5 and so on, I would like it to change the sheet names from Sheet2 to George, sheet3 to Sam, sheet4 to John, etc


    Is this possible?

  2. #2
    Board Regular
    Join Date
    Jan 2018
    Posts
    266
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Automatically name worksheets based on cell values from master sheet

    Quote Originally Posted by timlh42 View Post
    I would like to have names listed in sheet1 in column A, beginning at A3 and ending on A30.

    So if there is a name in A3 ( George) and (Sam) in A4, (John) in A5 and so on, I would like it to change the sheet names from Sheet2 to George, sheet3 to Sam, sheet4 to John, etc


    Is this possible?
    Code:
    Sub Re_Name_Worksheet()
    
    Dim WS_Names As Variant, Z As Long
    
    WS_Names = ActiveSheet.Range("A3:A30").Value
    
    With ThisWorkbook
    
        For Z = 2 To .Worksheets.count 'start at 2nd worksheet and then rename
        
           .Worksheets(Z).Name = WS_Names(Z - 1, 1)
        
        Next
    
    End With
    
    End Sub
    Last edited by MoshiM; Oct 11th, 2019 at 06:56 PM.

  3. #3
    Board Regular
    Join Date
    Sep 2017
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically name worksheets based on cell values from master sheet

    Excellent!!!!!

    Thank You!!!

  4. #4
    Board Regular
    Join Date
    Sep 2017
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically name worksheets based on cell values from master sheet

    For some reason I keep getting a run time error 1004 and the

    .Worksheets(Z).Name = WS_Names(Z - 1, 1)

    is highlighted

  5. #5
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,886
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Automatically name worksheets based on cell values from master sheet

    Try this:
    Code:
    Sub Sheet_Names()
    'Modified 10/14/2019 9:14:55 AM  EDT
    On Error GoTo M
    Application.ScreenUpdating = False
    Dim i As Long
    For i = 3 To 30
        Sheets(i - 1).Name = Sheets(1).Cells(i, 1).Value
    Next
    Application.ScreenUpdating = True
    Exit Sub
    M:
    MsgBox "There was a problem." & vbNewLine & "You may have a duplicate sheet name or a improper sheet name"
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  6. #6
    Board Regular
    Join Date
    Sep 2017
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically name worksheets based on cell values from master sheet

    Quote Originally Posted by My Aswer Is This View Post
    Try this:
    Code:
    Sub Sheet_Names()
    'Modified 10/14/2019 9:14:55 AM  EDT
    On Error GoTo M
    Application.ScreenUpdating = False
    Dim i As Long
    For i = 3 To 30
        Sheets(i - 1).Name = Sheets(1).Cells(i, 1).Value
    Next
    Application.ScreenUpdating = True
    Exit Sub
    M:
    MsgBox "There was a problem." & vbNewLine & "You may have a duplicate sheet name or a improper sheet name"
    End Sub
    Sorry but I still keep getting the above message.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,119
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Automatically name worksheets based on cell values from master sheet

    What is the error message?
    Also how many sheets do you have?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Sep 2017
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically name worksheets based on cell values from master sheet

    Quote Originally Posted by Fluff View Post
    What is the error message?
    Also how many sheets do you have?

    It is your embedded message. There was a problem. You may have a duplicate sheet name or a improper sheet name

    My sheets are as follows:

    Data, Comments, Grading, Sheet1, Sheet2, Sheet3, Sheet4, etc

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,119
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Automatically name worksheets based on cell values from master sheet

    It wasn't my message.
    When you ran the original code you got a 1004 error, what was the message that went with it?
    Can you also please answer my other question?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Sep 2017
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically name worksheets based on cell values from master sheet

    I have 7 sheets

    The 1004 error code says

    Appicatio- defined or object defined error

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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