You can use code like

Debug.Print ActiveCell.Name.Name

to get the name of the active cell. This, of course, assumes that the name
refers only to that cell, not to a larger range of which ActiveCell is one
cell. If you want to find the larger range(s) that contain some cell, you'll
have to loop through all the Names and use Intersect to see if the
RefersToRange encompasses the ActiveCell. For example,

Function NamesWithCell(R As Range) As Name()
Dim NN() As Name
Dim N As Name
Dim J As Long
On Error Resume Next
For Each N In ThisWorkbook.Names
If Not Application.Intersect(N.RefersToRange, R) Is Nothing Then
J = J + 1
ReDim Preserve NN(1 To J)
Set NN(J) = N
End If
Next N
NamesWithCell = NN
End Function

Private Function IsArrayAllocated(V As Variant) As Boolean
IsArrayAllocated = IsArray(V) And Not IsError(LBound(V, 1)) And
LBound(V) <= UBound(V)
End Function

Sub AAA()
Dim FoundNames() As Name
Dim J As Long
FoundNames = NamesWithCell(ActiveCell)
If IsArrayAllocated(FoundNames) = True Then
For J = LBound(FoundNames) To UBound(FoundNames)
Debug.Print FoundNames(J).Name,
FoundNames(J).RefersToRange.Address
Next J
Else
Debug.Print "no names found"
End If
End Sub

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


"MJKelly" wrote in message
...

Hi,

I am looping through multiple named ranges. Depending which named
range a value is in, I need to reference another named range
(basically Monday, Tuesday etc). How Can I Select Case to find the
named range of the current cell to determin which other named range to
move to? So a cell in MondayData named range will be reported in
MondayReport named range. I see this as an option for me to re-use
the code instead of using seven versions of the code (one for each
day).

Hope you can help?

kind regards,
Matt