Rob,

I like it. I was trying to use "Properties" but wasn't getting it right.
Now I understand how it works in this case.

Thanks,

Doug

"Rob van Gelder" wrote in message
...
Doug,

Here's one way, but there's no gain really. Better off just to use the
Sheet
Name.

Sub test()
Dim strCodeName As String, strSheetName As String

strCodeName = "Sheet1"

With Workbooks("Book2")
strSheetName =
.VBProject.VBComponents(strCodeName).Properties("N ame").Value
MsgBox .Worksheets(strSheetName).Range("A1").Value
End With
End Sub


Rob


"Doug Glancy" wrote in message
...
My 3rd try on this question, hopefully the charm...

There was discussion last week of the advantage of referring to a
worksheet
by it's code name, e.g., Sheet1 rather than it's tab name, as in
WorkSheets("mysheet") or by index, as in WorkSheets(1). My question
is how to refer to a sheet in another workbook. I've tried:

Workbooks("mybook").Sheet1 and other such variations, without success.

I can access a sheet by code name in another workbook as a VBComponent
e.g.,

Workbooks("Book1").VBProject.VBComponents.Item("Sh eet1")

but then I can't, for example, refer to a range within the component.

Thanks in advance for any help a with this,

Doug