To get the name you need to use '.Name' rather than '.Address'. I get no
error using your code but get the same error when using '.Name' when no name
exists in the relevant sheet-range.


For i = 1 To Sheets.Count
Debug.Print "Sheet " & i & " is named """ & Sheets(i).Name & """"
Debug.Print Range("'" & Sheets(i).Name & "'!A1").Name
Next i


The following will debug.print all names:

Sub WBNames()
For Each n In ActiveWorkbook.Names
Debug.Print n
Next n
End Sub


HTH

--
Steve

"tbone" wrote in message
...
I need to reference cells in VBA using sheet-level names, which I have
set up using Name Manager. But I ran into some troubles, so I tried to
boil it down to figure it out. I have arrived at the situation below,
and I am baffled as to why this doesn't work:

For i = 1 To Sheets.Count
Debug.Print "Sheet " & i & " is named """ & Sheets(i).Name &
""""
Debug.Print Range("'" & Sheets(i).Name & "'!A1").Address
Next i

I get:
Sheet 1 is named "Current"
$A$1
Sheet 2 is named "Past"
**error here**

Runtime error '1004':
Method 'Range' of object '_Worksheet' failed

I tried it with and without the apostrophes to delimit the sheet name.
I even copied the Current sheet into a new workbook in case there was
some hidden corruption. No luck.

Any ideas? What am I missing?

Thanks
tbone