Hi

In Excel2003 I have created this:

Private Sub cmdRename_Click()
Dim strOldName As String
Dim strNewName As String
Dim intRowLoop As Integer
Dim shtRename As Worksheet

intRowLoop = 2
Do
strOldName = Cells(intRowLoop, 1).Value
If Not IsEmpty(Cells(intRowLoop, 2)) Then
strNewName = Cells(intRowLoop, 2).Value
For Each shtRename In ThisWorkbook.Worksheets
If shtRename.Name = strOldName Then
shtRename.Name = strNewName
Cells(intRowLoop, 1).Value = strNewName
Cells(intRowLoop, 2).ClearContents
End If
Next
intRowLoop = intRowLoop + 1
End If
Loop Until IsEmpty(Cells(intRowLoop, 1))
End Sub

It expects on a control sheet the staff tabname CA1, CA2, ...
and next to it the names of the staf members.
It loops throu the names and checks if there is a new name next to it
if so, looks for tan tab with this name.
If it is found the name of the tab is changed as if the old name on
the control.
Also is the old name on the control sheet replaced and de new name
deleted.
If a staffmember at some point in the future is replaced this macro
van be used to change the apporpiate tabname.
If a new stafmember is added to the department it will stil work.

HTH,

Wouter