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]

Here is where your not being specific:
You said:
cells in the master sheet.

Does this mean the sheet is named "Master"

So if you enter "Alpha" In the Range("C17") of the sheet named "Master") you want what sheet named "Alpha"

And you said multiple sheets.

So do you want the script just in the Sheet named "Master"
to perform the task of renaming multiple sheets.

Or do you want the script in numerous sheets?

 

[gordsky]

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

the code should be pasted in the THISWORKBOOK module not as a sheet code
trh this, it will change what ever worksheet has C17 changed in

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
If Not Intersect(target, ActiveSheet.Range("C17")) Is Nothing Then

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

End If
End Sub

 

[IREY]

Here is where your not being specific:
You said:
cells in the master sheet.

Does this mean the sheet is named "Master"

So if you enter "Alpha" In the Range("C17") of the sheet named "Master") you want what sheet named "Alpha"

And you said multiple sheets.

So do you want the script just in the Sheet named "Master"
to perform the task of renaming multiple sheets.

Or do you want the script in numerous sheets?

Hi i apologize im new to VBA.
The sheet name that contains the cells that i want to rename other sheets is called is "START"
if i enter "Alpha" in C17 i want it to change the name of the sheet after "START"
The cells are spaced out by 2 so if i put "Beta" in cell C19 i want that to change the sheet after "Alpha" to "Beta" and so on for 13 sheets.
I would only need the script in "START" if that is possible.

 

[My Aswer Is This]

Here is a sample example of how the script should be written:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  3/8/2022  7:15:37 PM  EDT
If Not Intersect(Target, Range("C17")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Sheets("Alpha").Name = Range("C17").Value
End If
End Sub

 

[My Aswer Is This]

I realize a lot of Error catching code should be added.
Like if the name in Range("C17") is not a proper sheet name or already exist.
But until I know what you want I will not enter all the Error catching code.

 

[IREY]

Here is a sample example of how the script should be written:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  3/8/2022  7:15:37 PM  EDT
If Not Intersect(Target, Range("C17")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Sheets("Alpha").Name = Range("C17").Value
End If
End Sub

THANK YOU! this works for one cell and one sheet. how can i multiply it for C19 and other cells?

 

[IREY]

I need these cells

To auto rename these sheets when edited

 

[My Aswer Is This]

Well in your last posting how your wanting to do things seems very complicated.



Why would you want to do things this way?



And why not enter all the current sheet names In the Range("A1:A20")
And enter the new sheet names in the Range("B1:B20")

And the script runs one time and renames all the sheets.

 

[IREY]

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.