When you add sheets is the best time to edit its codename...

With ActiveWorkbook.Sheets.Add
.Name = "SheetName": .CodeName = "SheetCodeName"
End With

...or set an object variable to each sheet and ref by sheetname...

Dim wks As Worksheet
Set wks = Sheets("SheetName")
wks.CodeName = "SheetCodeName"

...if you're looping sheets in a workbook.

If you're assigning codenames based on existing sheetnames then you
might be better to store a set of delimited strings and work through
the set in order. (This requires the strings be sync'd)

Const sNames$ = "Sheet1,Sheet2,Sheet3,Sheet4"
Const sCodeNames$ =
"Sheet1Codename,Sheet2Codename,Sheet3Codename,Shee t4Codename"

Dim i%, vaNames, vaCodeNames

vaNames = Split(sNames, ","): vaCodeNames = Split(sCodeNames, ",")
For i = LBound(vaNames) To UBound(vaNames)
ActiveWorkbook.Sheets(vaNames(i)).Codename = vaCodeNames(i)
Next

Also, you might want to note that you can expect sheetnames to be
changed whereas codenames are likely to persist. In this case you can
use the codename to find a sheetname...

Dim wksSource As Worksheet
Set wksSource = Sheets(Get_SheetTabName(ActiveWorkbook,
"SheetCodeName")

Function Get_SheetTabName(Wkb As Workbook, CodeName As String) As
String
Dim Wks As Worksheet
For Each Wks In Wkb.Worksheets
If Wks.CodeName = CodeName Then _
Get_SheetTabName = Wks.name: Exit Function
Next
End Function

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion