Rename Multiple sheets Based on Cells in Mastersheet

[IREY]

Hi, Im having some trouble figuring out how to do this but i would like to rename multiple sheets based off what is inside cells in the master sheet.
I would like the sheet names to update automatically when the cells are updated.
Currently this is what ive tried using but it only works on cells inside of the sheet the code is pasted in.
I need to figure out how to edit this code to use cells from the mastersheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C17")) Is Nothing Then

If Range("C17") = Empty Then
ActiveSheet.name = "Client Unspecified-" & ActiveSheet.Index
Else
ActiveSheet.name = Range("C17")
End If

End If
End Sub

 

[My Aswer Is This]

A lot of people will be using this file and i want it to be easily customizable for the users needs. I can remove the spacing between cells if that makes it easier.

I do not understand your image. I see what appears to be row 17 to 32
And where in the image is the old sheet name and where in the image is the new sheet name;
What do I see in column B and is the new sheet name in column C

 

[IREY]

I need the cells highlighted yellow to change the names of the worksheets. C17 changes Sheet2 name, C19 to change sheet3 name, C21 to change sheet4 name and so on

 

[gordsky]

Maybe this is what you are after

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Columns(3)) Is Nothing Then

    If Target.row Mod 2 <> 0 Then
        Dim i As Long
        If Target.row = 17 Then
            i = 2
        Else
            i = ((Target.row - 17) / 2) + 2
        End If
       
        If Target = Empty Then
             Worksheets(i).name = "Client Unspecified-" & Worksheets(i).Index
        Else
             Worksheets(i).name = Target.Value
        End If
    Else
        End
    End If
End If
End Sub

 

[IREY]

Maybe this is what you are after

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Columns(3)) Is Nothing Then

    If Target.row Mod 2 <> 0 Then
        Dim i As Long
        If Target.row = 17 Then
            i = 2
        Else
            i = ((Target.row - 17) / 2) + 2
        End If
      
        If Target = Empty Then
             Worksheets(i).name = "Client Unspecified-" & Worksheets(i).Index
        Else
             Worksheets(i).name = Target.Value
        End If
    Else
        End
    End If
End If
End Sub

Thank you so much this worked! youre the best ive been working on this for days

 

[IREY]

Hey Im so sorry but is there a function you can add to change "/" to "-" ? when i use dates an error message pops up

 

[gordsky]

Hey Im so sorry but is there a function you can add to change "/" to "-" ? when i use dates an error message pops up

change

Worksheets(i).name = Target.Value

to

Worksheets(i).name = replace((Target.Value),"/","-")

 

[EdKnight]

This worked for me but i changed it to column A. How can I change it so it doesn't go up by 2 cells but only 1. So A1 changes sheet 2, A2 changes sheet 3, A3 changes sheet 4 etc.

 

[Joe4]

This worked for me but i changed it to column A. How can I change it so it doesn't go up by 2 cells but only 1. So A1 changes sheet 2, A2 changes sheet 3, A3 changes sheet 4 etc.

Duplicate to: VBA to rename sheet based on cell value in another VBA dependent sheet name

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will typicaally be locked or deleted.

As I mentioned in that other thread, please post your question to to its own thread.