Forum Discussion

jkpanic's avatar
jkpanic
Copper Contributor
Oct 16, 2019

Alphabet sort and worksheet sort

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

excel

3 Replies

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Oct 16, 2019

    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

     

     

    • jkpanic's avatar
      jkpanic
      Copper Contributor
      Oct 16, 2019

      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