Name of sheet is based on cell value

[ryan0521]

Happy new year to all,

Can you help me to name sheets in workbook based on cell value specifically (J2).

Thank you in advance.

 

[YasserKhalil]

May be

Sub Test()    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Range("J2").Value <> "" Then ws.Name = ws.Range("J2").Value
    Next ws
End Sub

 

[Michael M]

Try

Sub MM1()
ActiveSheet.Name = Range("J2").Value
End Sub

 

[ryan0521]

Do I really need to run the code first? before it will rename the sheet?

 

[svendiamond]

Do you want it to automatically rename the sheet any time cell J2 is changed?

 

[ryan0521]

Yes can you help me with that?

 

[YasserKhalil]

Try this in worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Address = "$J$2" Then
        Me.Name = Range("J2").Value
    End If
End Sub

Or if you need to apply on any sheet in the workbook try this in workbook module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address = "$J$2" Then
        Sh.Name = Range("J2").Value
    End If
End Sub

 

[Michael M]

Place this in the sheet module

Private Sub worksheet_Change(ByVal target As Range)
If Intersect(target, Range("J2")) Is Nothing Then Exit Sub
If Range("J2").Value <> "" Then ActiveSheet.Name = Range("J2").Value
End Sub

 

[ryan0521]

It works, but (J2) of sheet2 is link to sheet1 when I change the value of j2 in sheet1, sheet name for sheet1 changed but on sheet2 does not.

 

[Michael M]

I think maybe it's time you gave us ALL the required information....Each time we give you a solution, you move the goal posts !!