Worksheet Rename VBA - Referencing Cell on Another Sheet (Same Workbook)

[hamiltonhere]

Hello,
I have the following code that only works if I am referring to a cell on the same worksheet that the code is being used on. I would like Range("A1") to actually be Cell B10 on a worksheet which is called "Master". How would I alter this code to accomplish this? Thanks in advance!

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then
If Range("A1") = Empty Then
ActiveSheet.name = "Client Unspecified-" & ActiveSheet.Index
Else
ActiveSheet.name = Range("A1")
End If
End If
End Sub

 

[bebo021999]

To trigger the change of cell B10 in the "Master" sheet (through manual change), the active sheet must be the "Master" sheet.
Assuming the sheet you want to rename is Sheet1 (based on the sheet index).This code is placed in the module of the "Master" sheet:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B10")) Is Nothing Then Exit Sub
With Sheet1
    If IsEmpty(Target) Then
        .Name = "Client Unspecified-" & ActiveSheet.Index
    Else
        On Error Resume Next
        .Name = Target
        If Err.Number <> 0 Then
            MsgBox "Invalid sheetname! Try again"
        End If
        On Error GoTo 0
    End If
End With
End Sub

 

[hamiltonhere]

Thank you! That worked! I am now trying to figure out how to continue that same pattern, where Sheet2 will get renamed to the value that is in Cell D10, Sheet3 will be renamed to the value in Cell F10, Sheet4 will be renamed to the value in Cell H10, etc. I have 30 sheets that would follow this pattern. Any idea how to accomplish this?