Alphabet sort and worksheet sort

[jkpanic]

Hi,

I have a workbook with multiple worksheets and was kindly given some code that enables me to type a name in worksheet 1 (called Training Matrix) and this will change the name on the worksheet that relates to that row number. If there is no worksheet it creates one with the name typed in the "Training Matrix". However if I do an A-Z sort on "Training Matrix" it does not change the order of the other worksheets. How can I get it to do this?

Also if I delete a name on "Training Matrix" it does not delete the corresponding worksheet tab name.

Thanks

[#2]

@jkpanic 

Please replace the existing code with the following one and see if that works as desired...

Dim wsName As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim ws As Worksheet

On Error GoTo Skip
If Not Intersect(Target, Range("SheetNames")) Is Nothing Then
    Application.EnableEvents = False
    If Target <> "" Then
        If wsName = "" Then
            Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
            ws.Name = Target.Value
        Else
            Set ws = Worksheets(wsName)
            ws.Name = Target.Value
        End If
    End If
    wsName = ""
End If
Application.EnableEvents = True
Exit Sub
Skip:
Application.Undo
Application.EnableEvents = True
MsgBox Err.Description, vbExclamation, Err.Number
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("SheetNames")) Is Nothing Then
    Application.EnableEvents = False
    wsName = Target.Value
    Application.EnableEvents = True
End If
End Sub

[#3]

@Subodh_Tiwari_sktneer 

Hi,

Thank you but this does not quite achieve what I want. It creates the worksheet with the new name but does not delete the worksheet or the worksheet name if I delete it from "Training Matrix".

Also if I add a name to the bottom of the list on "Training Matrix" and then sort alphabetically it does not reorder the worksheets alphabetically, is this possible?

Cheers

Julian

[#4]

Hi Julian,

Sorry, I missed that requirement.

Please test the attached and let me know if it works as desired now.