You are attempting to mix two object chains that really aren't
intended to be mixed. VBA itself knows of a worksheet only via its
CodeName, which as you correctly state doesn't change if you change
the name of the worksheet. But you're trying to mix that with an
object known only to Excel, not VBA, namely a Workbook. There is no
particularly good remedy. If your primary goal is to prevent problems
that may occur when the user changes a worksheet name, use a defined
name on that sheet and then use the Worksheet property of the Range to
get the sheet and then from the sheet get some cell. E.g.,


Debug.Print Range("TheName").Worksheet.Range("A1").Value

Here, the range A1 will point to the worksheet on which the name
TheName resides.

If you use the codename to reference a sheet, as in

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

That sheet will always refer to the ThisWorkbook workbook. You cannot
"redirect" the Sheet1 reference to another workbook.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Fri, 4 Dec 2009 23:02:19 -0700, "Robert Crandal"
wrote:

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