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