Change Tab Names Based on Values of Cells on a Different Sheet

[LMorr]

Hello,

I am trying to write code in the attached file so that the names of the "sub" and "Tline" tabs will change based on the values placed in cells A7-A34 on sheet "Funding Project Est Summary". For Ex if I make cell A7 "test" then the tabs currently named "Sub DPN-01" and "Sub DPN-01 Detail" should change to "test" and "test detail." I am using the code posted below. The code works put it throws an error "Run-time error 1004. That name is already taken. Try a differnet one." I'm not sure why as the name is not already taken. What is causing this error message? Also, I would like to add an error handler that would prompt the user to try again if the cell is left blank or if a duplicate name is used. I am not sure how to approach that given my code. Any help is appreciated!

>>> use - code tags <<<

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A7:A34")) Is Nothing Then
       Sheets(4).Name = Range("A7")
        Sheets(5).Name = Range("A7") + " " + "Detail"
        Sheets(6).Name = Range("A8")
        Sheets(7).Name = Range("A8") + " " + "Detail"
        Sheets(8).Name = Range("A9")
        Sheets(9).Name = Range("A9") + " " + "Detail"
        Sheets(10).Name = Range("A10")
        Sheets(11).Name = Range("A10") + " " + "Detail"
        Sheets(12).Name = Range("A11")
        Sheets(13).Name = Range("A11") + " " + "Detail"
        Sheets(14).Name = Range("A12")
        Sheets(15).Name = Range("A12") + " " + "Detail"
        Sheets(16).Name = Range("A13")
        Sheets(17).Name = Range("A13") + " " + "Detail"
        Sheets(18).Name = Range("A14")
        Sheets(19).Name = Range("A14") + " " + "Detail"
        Sheets(20).Name = Range("A19")
        Sheets(21).Name = Range("A19") + " " + "Detail"
        Sheets(22).Name = Range("A21")
        Sheets(23).Name = Range("A21") + " " + "Detail"
        Sheets(24).Name = Range("A22")
        Sheets(25).Name = Range("A22") + " " + "Detail"
        Sheets(26).Name = Range("A23")
        Sheets(27).Name = Range("A23") + " " + "Detail"
        Sheets(28).Name = Range("A24")
        Sheets(29).Name = Range("A24") + " " + "Detail"
        Sheets(30).Name = Range("A25")
        Sheets(31).Name = Range("A25") + " " + "Detail"
        Sheets(32).Name = Range("A26")
        Sheets(33).Name = Range("A26") + " " + "Detail"
        Sheets(34).Name = Range("A27")
        Sheets(35).Name = Range("A27") + " " + "Detail"
        Sheets(36).Name = Range("A28")
        Sheets(37).Name = Range("A28") + " " + "Detail"
        Sheets(38).Name = Range("A33")
        Sheets(39).Name = Range("A33") + " " + "Detail"
        Sheets(40).Name = Range("A15")
        Sheets(41).Name = Range("A15") + " " + "Detail"
        Sheets(42).Name = Range("A16")
        Sheets(43).Name = Range("A16") + " " + "Detail"
        Sheets(44).Name = Range("A20")
        Sheets(45).Name = Range("A20") + " " + "Detail"
        Sheets(46).Name = Range("A17")
        Sheets(47).Name = Range("A17") + " " + "Detail"
        Sheets(48).Name = Range("A18")
        Sheets(49).Name = Range("A18") + " " + "Detail"
        Sheets(50).Name = Range("A29")
        Sheets(51).Name = Range("A29") + " " + "Detail"
        Sheets(52).Name = Range("A30")
        Sheets(53).Name = Range("A30") + " " + "Detail"
        Sheets(54).Name = Range("A31")
        Sheets(55).Name = Range("A31") + " " + "Detail"
        Sheets(56).Name = Range("A32")
        Sheets(57).Name = Range("A32") + " " + "Detail"
        Sheets(58).Name = Range("A34")
        Sheets(59).Name = Range("A34") + " " + "Detail"
       
    End If

End Sub

 

[Marc L]

Hello, as the error message is always right …​

​

Anyway, according to your attachment a smart worksheet event VBA procedure sample :​

Private Sub Worksheet_Change(ByVal Target As Range)
   With Application
        If Target.CountLarge > 1 Or .Intersect(Target, [A7:A34]) Is Nothing Then Exit Sub
        L& = Index + 1 + (Target.Row - 7) * 2
    If .CountIf([A7:A34], Target) > 1 Or Target = "" Or Trim(Target) <> Target Or Sheets.Count <= L Then
        Beep
       .EnableEvents = False
       .Undo
       .EnableEvents = True
    Else
        Sheets(L).Name = Target
        Sheets(L + 1).Name = Target & " Detail"
    End If
   End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[LMorr]

Hello, as the error message is always right …​

​

Anyway, according to your attachment a smart worksheet event VBA procedure sample :​

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 And Not Intersect(Target, [A7:A34]) Is Nothing Then
            L& = Index + 1 + (Target.Row - 7) * 2
        If Application.CountIf([A7:A34], Target) > 1 Or Target = "" Or Trim(Target) <> Target Or Sheets.Count <= L Then
            Beep
            Application.Undo
        Else
            Sheets(L).Name = Target
            Sheets(L + 1).Name = Target & " Detail"
        End If
    End If
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

Thank you! This solved my issue. I knew that a loop was a smarter way to get the job done but I'm not familiar enough with the syntax of VB to accomplish that. I appreciate your help!

 

[Marc L]

As obviously here a loop is very not necessary, the reason why my VBA procedure sample does not use any !​

​

My previous post procedure edited for some optimization …​

​