You might be able to do what you want like this...

Dim CN As String, WS As Worksheet
......
......
CN = "Sheet1"
For Each WS In Workbooks("RicksSaveAsTest.xls").Worksheets
If WS.CodeName = "Sheet1" Then Exit For
Next

After running the above code, WS (as long as it is in scope) will be set to
the worksheet with CodeName "Sheet1" no matter what the user has renamed it.
You can see this be executing this line after the Next statement...

MsgBox "Current sheet name for CodeName 'Sheet1': " & WS.Name & _
vbLf & "Contained in Workbook: " & WS.Parent.Name

This statement shows that as long as WS is in scope, you can use WS to refer
to the worksheet with CodeName "Sheet1".

--
Rick (MVP - Excel)


"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