Resolved >>> Adjust code to include sorting employee sheets a-z?

Hi to Ikaabod and anyone else that may be able to help.

Since asking for help I have written my first bit of VBA code all by myself! (although admittedly used some help files to adapt to my requirements someone elses code).

As you may remember, Ikaabod wrote some code to help me create new absence sheets using a user form.

I was wondering how I would add in code to sort the worksheets from A-Z.

Also, Ideally, I would like to hide the template sheet that the new records are copied from. Is it possible to add code that would unhide the resulting worksheet once it is copied and renamed?

I am learning loads from this forum, so thank you once again.

Here is the code in it's current state:

Private Sub cmdConfirmNewStarter_Click()
    Dim strNewWB, strNewWB2 As String
    Dim iLastRow As Long
    Dim i As Integer
    Dim myCheck As Boolean
    
    
    'Make sure Sheet Name "Surname, Initial" does not exist
    'If it exists, add appropriate number to the Sheet Name
    strNewWB = frmNewStarterSetUp.tbxSurname & ", " & frmNewStarterSetUp.tbxInitial
    strNewWB2 = strNewWB
    i = 2
    Do
        myCheck = False
        For Each wsh In Worksheets
            If wsh.Name = strNewWB2 Then
                strNewWB2 = strNewWB & " (" & i & ")"
                myCheck = True
            End If
        Next wsh
        i = i + 1
    Loop Until myCheck = False
    strNewWB = strNewWB2
    
    '1.Make a copy of the Template Sheet
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    '7.Rename the sheet (preferably using "Surname, Initial.")
    Sheets(Sheets.Count).Name = strNewWB
    
    With Sheets(strNewWB)
        '2.Put the Surname into cell E3
        .Range("E3") = frmNewStarterSetUp.tbxSurname.Value
        '3.Put the Initial into cell R3
        .Range("R3") = frmNewStarterSetUp.tbxInitial.Value
        '4.Put the ERN (Employee Number) into cell AC3
        .Range("AC3") = frmNewStarterSetUp.tbxERN.Value
        '5.Put the Job Title into cell E4
        .Range("E4") = frmNewStarterSetUp.cboJobTitle.Value
        '6.Put the Start Date into cell AC4
        .Range("AC4") = frmNewStarterSetUp.tbxStartDate.Value
    End With
    With Sheets("All Employees - Absence Summary")
        '8.Put the Surname into the "All Employees - Absence Summary" worksheet
        'in the next available row of column B
        iLastRow = .Range("B" & Rows.Count).End(xlUp).Row
        .Range("B" & iLastRow + 1) = frmNewStarterSetUp.tbxSurname.Value
        '9.Put the Initial... in the next available row of column C
        .Range("C" & iLastRow + 1) = frmNewStarterSetUp.tbxInitial.Value
        '10.Put the ERN... in the next available row of column D
        .Range("D" & iLastRow + 1) = frmNewStarterSetUp.tbxERN.Value
        '11.Put the Start Date... in the next available row of column E
        .Range("E" & iLastRow + 1) = frmNewStarterSetUp.tbxStartDate.Value
    
    End With
    'If you want to do more than one entry can get rid of line "unload me"
    'and copy code from "cmdClearForm_Click".  It's up to you
    frmNewStarterSetUp.tbxSurname.Value = ""
    frmNewStarterSetUp.tbxInitial.Value = ""
    frmNewStarterSetUp.tbxERN.Value = ""
    frmNewStarterSetUp.tbxStartDate.Value = ""
    frmNewStarterSetUp.cboJobTitle = ""
    frmNewStarterSetUp.tbxSurname.SetFocus
    End Sub
    
Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cmdClearForm_Click()
    frmNewStarterSetUp.tbxSurname.Value = ""
    frmNewStarterSetUp.tbxInitial.Value = ""
    frmNewStarterSetUp.tbxERN.Value = ""
    frmNewStarterSetUp.tbxStartDate.Value = ""
    frmNewStarterSetUp.cboJobTitle = ""
    frmNewStarterSetUp.tbxSurname.SetFocus
End Sub

Private Sub UserForm_Initialize()

tbxSurname.Value = ""
tbxInitial.Value = ""
tbxERN.Value = ""
tbxStartDate = ""

With cboJobTitle
.AddItem "Catalogue Co-ordinator"
.AddItem "Catalogue Mailing Manager"
.AddItem "Clerical Team Manager"
.AddItem "Clerk"
.AddItem "Depot General Manager"
.AddItem "Driver"
.AddItem "Driver Team Manager"
.AddItem "In-depot Worker"
.AddItem "Night Sorter"
.AddItem "Night Team Manager"
.AddItem "Operations Manager"
End With
cboJobTitle.Value = ""

End Sub

bump for evening

For worksheet sorting, take a look at Chip Pearson's explanation and code: HERE

The following code will hide and then unhide a worksheet named "Template"

ThisWorkbook.Sheets("Template").Visible = xlSheetHidden
ThisWorkbook.Sheets("Template").Visible = xlSheetVisible

Thanks Ikaabod, I have got the copied sheet to become visible now.

I have read the help sheet you pointed to, & it looks exactly what I am looking for, but I don't understand where I would put it in my existing code to make it part of the process in making a new absence sheet.

In other words, I was hoping that after copying & renaming the sheet to match the userform inputs, it would then sort the sheets.

I have looked at it several times, but can't figure it.

You can put the code from the link in a new Module and then put something like this:

mySorting = SortWorksheetsByName(2, Sheets.Count, "")

after your code:

'7.Rename the sheet (preferably using "Surname, Initial.")
    Sheets(Sheets.Count).Name = strNewWB

The "2" will likely need to be changed as your needs require, but that will sort sheets starting from your second sheet all the way to the sheet furthest to the right.

Thank you once again!

The code does exactly what I wanted it to. This forum has really inspired me to get to grips with VBA.

I intend to get a good book and digest! I think once I get a handle on the basics, i.e what a module actually is, etc, everthing will become clear.