Have a Template rename its tab after open/duplicate

[gheyman]

I have a template that I keep hidden. I am using a button to unhide it, unprotect it, make a copy and then close it: The protection part is done because I have R1 and R2 protected

Sub CopyTemplate()
 
    Sheet5.Visible = True
    Sheets("Template").Unprotect Password:="Password"
    Sheets("Template").Select
    Range("R2").Clear
    
    Sheets("Template").Copy After:=Sheets(5)
    
    Sheets("Template").Select
    Range("R2").Value = "Template"
    Sheets("Template").Protect Password:="Password"
    ActiveWindow.SelectedSheets.Visible = Fale
   
    
End Sub

I want the to be able to rename the tab based on whats in R1 - but I also want to ensure that the tab always is named after R1 which is dynamic based on data in each tab.

R1 =CONCATENATE(O1,"_",E10,"_",E2)

I put this code in the template tab (therefore each of its copies) for renaming. Remember if the user makes a change to O1, E10 or E2 I want the new tab (copy of the template to rename itself. This is the area I have difficult in. This formula get an error message when I tab through diffferent sheets in the workbook.

Private Sub Worksheet_Activate()
   Dim shName As String
    shName = Range("R1")
    If Name <> "" And LCase(Range("R2")) <> "template" Then Me.Name = shName
End Sub

I tried moving it to deactivate but same error. I dont really care where it goes as long as it checks the tab and ensures its name equals the latest value in R1

Thanks

and thanks to all prior how helped indirectly with the tab part.

 

[jasonb75]

See if this works for what you need.

Private Sub Worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("O1,E10,E2")) Is Nothing Then
    Dim shName As String, ws As Worksheet
        shName = Range("R1")
    On Error Resume Next
        Set ws = Worksheets(shName)
    If Not ws.Name = shName Then
        If shName <> "__" And LCase(Me.Name) <> "template" Then Me.Name = shName
    End If
    On Error GoTo 0
End If
End Sub

 

[gheyman]

Thank you!