I'd use:

Debug.print sheets(i).range("A1").address(external:=true)

This will include the workbook, worksheet and address.

===
If your code is in a general module, then I would think it would work -- if all
the sheets are worksheets.

But if your code is behind a worksheet, you could also try:

debug.print application.range("'" & ....



tbone wrote:

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

--

Dave Peterson