Need help: debug/fix VBA code to create new worksheets from template and names list

[Ruede]

Hi Everyone


I would really appreciate some help with debugging the attached VBA code (created in Excel 2021) and getting the last sorting function to work.


The purpose of the code is to:
(1) create new worksheets based on a template (named aa_template) and with names that correspond to the list in the first column of the active worksheet (aa_Master),
and then...
(2) sort the worksheets alphabetically.


The code mostly works. It creates new worksheets based on the template and list of names, but... it :
- throws up an "Execution error 1004: Name' method of object '_Worksheet' failed" and halts
- does not sort the worksheets alphabetically.


Thankyou in advance


Ruede

-----------

Sub Create()
    Dim myArray As Variant, rng As Range, rngLoop As Range, ws As Worksheet, ShCount As Integer, i As Integer, j As Integer
 
    'Error warning: missing template
    If Not SheetExists("aa_Template") Then
        MsgBox "The Template sheet does not exist. Make sure the Template is included before processing.", vbCritical + vbOKOnly
        Exit Sub
    End If
    
    'Optimise code
    Application.ScreenUpdating = False
    
       
    'Create new worksheets from names in first column of aa_Master worksheet
    With ActiveWorkbook.Sheets("aa_Master")
        Set rng = .Range("B2", "B" & .Cells(Rows.Count, "B").End(xlUp).Row)
        For Each rngLoop In rng
            If Not SheetExists(rngLoop.Value) Then
                ActiveWorkbook.Sheets("aa_Template").Copy after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
                Set ws = ActiveSheet
                ws.Name = rngLoop.Value
            Else
                Set ws = ActiveWorkbook.Sheets(rngLoop.Value)
            End If
        Next
        .Activate
    End With
    
    'Sort worksheets alphabetically
    ShCount = Sheets.Count


        For i = 1 To ShCount - 1
        For j = i + 1 To ShCount
             If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
             Sheets(j).Move before:=Sheets(i)
             End If
        Next j
        Next i
     
    Application.ScreenUpdating = True
    
End Sub


Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
   Dim sht As Worksheet


    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    Set sht = wb.Sheets(shtName)
    On Error GoTo 0
    SheetExists = Not sht Is Nothing
End Function