Worksheet renaming question

[Am5687]

I am adapting an old workbook for a new training program. I would like to rename each sheet by employee name but the way it is structured that format is not working. The cover sheet has a drop down to navigate to the employee page but it will not display the name unless i name the sheets 1,2,3.
The formulas the cover sheet are using are as follows..

Cell A1
=IFERROR(INDIRECT("'"&ROW(A1)&"'!A2"), "") This is the list for employees names it pulls from the employee worksheet

Cell B1
=MATCH(C1, $A1:$A150,0)

The Dropdown is in Cell C1

The Code for the sheet is .....

Private Sub Worksheet_Change(ByVal Target As Excel.Range)    If Target.Column = 3 Then
    If Target.Row = 1 Then
        iRow = Cells(1, 2).Value
        Sheets(iRow + 1).Activate
        End If
    End If
End Sub


Private Sub CommandButton1_Click()
  'Add Sheets and Rename From Cover Sheet Command Button
     Dim MySheetName As String
        Dim SheetCount As Long
          Dim newname As String
          newname = InputBox("Enter New Employee Name")
           SheetCount = 2
            MySheetName = ""
             Sheets(SheetCount).copy After:=Sheets(SheetCount)
              ActiveSheet.Name = newname
 
End Sub

My question is what can i do to get this to allow me to add worksheets by the employee name. If that is to hard then i can leave it as is just need to get the code adjusted to add the sheet after the last sheet added and figure out a way to get the list to update if an employee is let go. as it is now if a sheet gets deleted is messes the whole list and dropdown up.Thanks for any help.

 

[ravishankar]

Sub namesheets()
Dim a As Long, x As Long
x = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To x
Sheets.Add.Name = Sheets("sheet1").Cells(a, 1)
Next a
MsgBox "complete"
End Sub

try this on a list in col A of sheet1.
ravishankar