Perhaps you could loop worksheet names until you find your hard-coded
CodeName. Or store all in a collection, eg

Dim mColWSnames As Collection

Sub SetWScollection(wb As Workbook)
Dim ws As Worksheet

Set mColWSnames = New Collection
For Each ws In wb.Worksheets
mColWSnames.Add ws.Name, ws.CodeName
Next

End Sub

Sub Test()
Dim sCodename As String
Dim ws As Worksheet

sCodename = ActiveWorkbook.Worksheets(2).CodeName 'normally already known
ActiveWorkbook.Worksheets(2).Name = "NewName"
SetWScollection ActiveWorkbook

Set ws = ActiveWorkbook.Worksheets(mColWSnames("Sheet2"))
MsgBox ws.Name, , sCodename
End Sub

Regards,
Peter T


wrote in message
ups.com...
Hi all,

I was wondering if there is anyway to reference a sheet by codename
that is in a different workbook than the one in which your macro
resides. The reason is, I need to access specific series of sheets
arbitrary in number in a series of other workbooks, the sheet names are
a character string as in "X sheet 1 of Y" Where Y is unknown; however,
the codenames are an incremented number, which would be much easier to
deal with.

Thanks for you help in advance,
Chris