I still think that it's a job better done manually--no matter who does it.

But...

Option Explicit
Sub auto_open()

Dim wks As Worksheet
Dim myName As String
Dim repWks As Worksheet

For Each wks In ThisWorkbook.Worksheets
If LCase(wks.Name) Like ("rep*") Then
'keep going
Set repWks = wks
Exit For
Else
MsgBox "no more sheets to rename"
Exit Sub
End If
Next wks

myName = InputBox(prompt:="enter a name")

If Trim(myName) = "" Then
Exit Sub
End If

Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(myName)
On Error GoTo 0

If wks Is Nothing Then
On Error Resume Next
repWks.Name = myName
If Err.Number < 0 Then
MsgBox "Invalid name, rename failed"
Err.Clear
End If
On Error GoTo 0
End If
End Sub

When you're happy, you could get rid of that msgbox about no more sheets to
rename.

sharpie00 wrote:

Actually, it will be for managers to use for their employees.

In essence, each employee will have the same worksheet within that
workbook. The only difference is that each sheet tab will have a
different name. It will be up to the manager to input the names
correctly.

Thanks.

--

Dave Peterson