Sub GlobalName()
Dim QueryRange As Range
Sheets(1).Activate
ActiveWorkbook.Names.Add Name:="GlobalName", RefersToR1C1:= _
"=Sheet1!R1C1:R3C1"
Sheets(2).Activate
'this works
Set QueryRange = Range("GlobalName")
MsgBox "Msg1" & vbLf & "Sheet Name: " & QueryRange.Parent.Name & vbLf & _
"Address: " & QueryRange.Address
Set QueryRange = Nothing
'this works even though active sheet is sheet2 and reference is on
'sheet1 plus I am including a sheet name.
Set QueryRange = Range("Sheet2!GlobalName")
MsgBox "Msg2" & vbLf & "Sheet Name: " & QueryRange.Parent.Name & vbLf & _
"Address: " & QueryRange.Address
Set QueryRange = Nothing
'this works even though active sheet is sheet2
Set QueryRange = Range("Sheet1!GlobalName")
MsgBox "Msg3" & vbLf & "Sheet Name: " & QueryRange.Parent.Name & vbLf & _
"Address: " & QueryRange.Address
Set QueryRange = Nothing
'this works where I express the same sheet as the reference
Set QueryRange = Sheet1.Range("Sheet3!GlobalName")
MsgBox "Msg4" & vbLf & "Sheet Name: " & QueryRange.Parent.Name & vbLf & _
"Address: " & QueryRange.Address
Set QueryRange = Nothing
'this fails
On Error GoTo ErrH
Set QueryRange = Sheet3.Range("GlobalName")
MsgBox "Msg5" & vbLf & "Sheet Name: " & QueryRange.Parent.Name & vbLf & _
"Address: " & QueryRange.Address
Set QueryRange = Nothing
ErrH:
MsgBox "Error #: " & Err.Number & vbLf & "Description: " & Err.Description
ActiveWorkbook.Names("GlobalName").Delete
Set QueryRange = Nothing
End Sub