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
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$J$2" Then
Me.Name = Range("J2").Value
End If
End Sub
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