For named ranges I find it is best to store an additional sheet within the workbook. 3 columns (name of range, sheet name, description)
That way you can find the exact range and sheet. Something like below
Dim NamedRange As String, WorksheetName as String
Dim Fnd As Range, Rng As Range
NamedRange = Sheets(xxx).Cells(1,1).Value
Set Fnd = Sheets("NamedRangeInfo").Range("A:A").Find(NamedRange, , , xlWhole, , , False, , False)
If Fnd Is Nothing Then
MsgBox "Unable to Find Named Range in the information sheet", vbinformation, "Error..."
WorksheetName = Fnd.Offset(0,1).Value
Set Rng = ThisWorkbook.Sheets(WorksheetName).Range(NamedRange)
Application.Goto Reference:= Rng
Set Rng = Nothing
Set Fnd = Nothing
Actually I have another related question...
That solution worked fine where the range name contained the location as a range name. But I also want to make the macro select a cell (ie go to that cell), where the cell address (eg C45) is given by the content of another cell. The content varies.