Results 1 to 7 of 7

Thread: Rename tab on cell value from another worksheet

  1. #1
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0

    Rename tab on cell value from another worksheet

    Hi All,

    I need a help to make my code work. I'm trying to rename worksheet one at a time but i'm having problem in using cell reference. see my code below and thanks in advanced for your help


    Code:
    Sub rename()
    
    Application.ScreenUpdating = False
    On Error GoTo error_handler
    
    
    If Range("C2") = "sports" Then
        Sheets(Format(Range("B2").Value)).Select
    '    ActiveSheet.Name = need to get the active sheet's new name from Xref sheet D2
        On Error GoTo error_handler
        
    Exit Sub
       
    
    End If
    
    error_handler:
    
        Sheets("Main").Select
        MsgBox ("Sheet name already exist")
    
    Application.ScreenUpdating = True
    
    End Sub
    Attached Files Attached Files
    Last edited by jeremiah_j2k; 12-02-2014 at 09:41 AM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Is this what you are after ?

    Code:
    Option Explicit
    
    Sub RenameSheet()
    
        Dim Sht As Worksheet, ShtName As String
        
        On Error Resume Next
        With Worksheets("Sheet2")
            '//new sheet name
            ShtName = .Range("b2").Value
            '//check the sheet is already exists
            Set Sht = Worksheets(ShtName)
        End With
        If Sht Is Nothing Then
            ActiveSheet.Name = ShtName
        Else
            MsgBox "Sheet name '" & ShtName & "' already exist"
        End If
    
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0
    Hello Admin,

    Sorry for the confusion because I guess I did not explain the problem clearly. From the attached file in post #1, the button and code will run in the "Main" tab. B2 is a dropdown list that contains the names of all the tabs that I need to rename based on a particular category in C2.

    For example;

    If "transport" is selected in C2, the sheet selected in B2 (e.g. Sheet4) will be named "bus".

    The "bus" value is located in Xref!D2. The code should get the name of Sheet4 from Xref!D2


    Thank you Admin
    Last edited by Admin; 12-02-2014 at 08:31 AM.

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Still not clear

    There is no value in C2, I think you are talking about A2

    Now where is Sheet4 ? How could I know sheet4 is to be renamed ?
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0
    I have uploaded the wrong file in post #1 but already changed it with the correct one.

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    OK.

    try

    Code:
    Sub RenameSheet()
    
        Dim Sht As Worksheet, ShtName As String, NewShtName As String
        
        On Error Resume Next
        With Worksheets("Main")
            '//new sheet name
            ShtName = .Range("b2").Value
            '//check the sheet is already exists
            Set Sht = Worksheets(ShtName)
            
            NewShtName = Evaluate("Xref!d2")
        
            If Not Sht Is Nothing Then
                Err.Clear
                Sht.Name = NewShtName
                If Not Err.Number = 0 Then
                    MsgBox "Sheet name '" & NewShtName & "' already exist"
                    Exit Sub
                End If
                .Columns(1).Replace ShtName, NewShtName, 1
            Else
                MsgBox "Sheet name '" & ShtName & "' not found"
            End If
        End With
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #7
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0
    Thanks for the code admin... its working perfectly as needed and i really appreciate it.. thanks
    Last edited by jeremiah_j2k; 12-02-2014 at 03:01 PM.

Similar Threads

  1. Change Tab Color base on value of cell % Change
    By mrprofit in forum Excel Help
    Replies: 8
    Last Post: 04-16-2014, 10:38 AM
  2. Worksheet Tab Name Based on Cell Value
    By tuna666 in forum Excel Help
    Replies: 2
    Last Post: 10-08-2013, 10:41 PM
  3. Need help to add sheet, rename and update in TOC
    By kets0985 in forum Excel Help
    Replies: 2
    Last Post: 07-28-2013, 06:04 PM
  4. Copy Template Worksheet And Rename With Custom Name
    By peter renton in forum Excel Help
    Replies: 5
    Last Post: 06-07-2013, 03:50 PM
  5. Rename Filename Through VBA
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 08-30-2011, 08:29 PM

Posting Permissions

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