|It would be easier to automate this if you would be willing to change the value in J8 and have it update the sheet name vs. changing the sheet name and have it update J8.|
The Worksheet_Change event can be used to run a macro whenever a specific cell is changed. Therefore changing J8 could trigger a macro the change the sheet name.
For example, this code will change the name of the sheet to whatever is entered in J8, assuming that it is a legal sheet name. (Error handling could be added to deal with situations where an illegal name was entered in J8)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$8" Then
Me.Name = Range("J8")
However, there is no WorksheetName_Change event, so there is no direct way to have J8 change when the sheet name is changed. Some other action would to be taken, such as activating another sheet.
For example, if this code was used, you could change the sheet name but J8 would not be updated until the sheet was Deactivated by clicking the sheet tab for another sheet:
Private Sub Worksheet_Deactivate()
Range("J8") = Me.Name
Let me know if either of those options sound like they would work for you.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03