If you have xl2002 (or higher) and you have
tools|macro|security|Trusted sources tab|trust access to VBA Project
unchecked, then the first method won't work:

Option Explicit
Sub testme01()

Dim myWks As Worksheet
Set myWks = Nothing
With Workbooks("book1.xls")
On Error Resume Next
Set myWks = .Worksheets(.VBProject.VBComponents.Item("Sheet1") .Name)
If Err.Number < 0 Then
MsgBox "something went wrong"
Err.Clear
Else
MsgBox myWks.Name & vbLf & myWks.Range("a1").Value
End If
On Error GoTo 0
End With

End Sub

But you can loop through the worksheets and find a match:

Sub testme02()

Dim wks As Worksheet
Dim myWks As Worksheet

Set myWks = Nothing
For Each wks In Workbooks("book1.xls").Worksheets
If LCase(wks.CodeName) = "sheet1" Then
Set myWks = wks
Exit For
End If
Next wks

If myWks Is Nothing Then
MsgBox "not found"
Else
MsgBox myWks.Name & vbLf & myWks.Range("a1").Value
End If

End Sub


Doug Glancy wrote:

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

--

Dave Peterson