Sub Tester()
Dim wb As Workbook, sht As Worksheet
Set wb = Workbooks("Book2.xls")

Set sht = GetSheetByCodeName(wb, "Sheet1")
If Not sht Is Nothing Then
MsgBox sht.Range("A1").Value
Else
MsgBox "No sheet with codename 'Sheet1'"
End If

End Sub

Function GetSheetByCodeName(wb As Workbook, sName As String)
Dim s As Worksheet, rv As Worksheet
For Each s In wb.Worksheets
If s.CodeName = sName Then
Set rv = s
Exit For
End If
Next s
Set GetSheetByCodeName = rv
End Function


Tim



"Robert Crandal" wrote in message
...
I wanted to avoid using the Worksheets("Sheet1") method
because this references the string name on the sheet tab.
Therefore, if a user renames "Sheet1" on the tab to something
else, then your code below will NOT work.

If you want to refer to first sheet on your workbook, without
referring to the string on the tab, you can use the code below:

Sheet1.Range("A1").Value = 100

How would you specificy a workbook using this notation above??


"J_Knowles" wrote in message
...

Workbooks("mybook.xls").Worksheets("Sheet1").Range ("A1").Value = 100