Looping through all named ranges in excel VBA in current active sheet -

hi looking loop through named ranges found in activeworksheet , them. i've used code below not seem produce anything. also, if can loop through named ranges contain words. example, named ranges named data1, data2, data3 , on. work on them if contain word data.

for each nm in activesheets.names     msgbox "nm.name" next nm 

if want names active worksheet, use this:

sub test() each nm in activeworkbook.names     if nm.referstorange.parent.name = activesheet.name msgbox nm.name next nm end sub 

^ code returned named ranges refer ranges on active worksheet.

nm.referstorange.parent return worksheet associated range.

we can retrieve name using .name , compare activeworksheet name.

here can see have 2 named ranges on sheet4 , 1 on sheet3

when run code, returns myname1 , myname2 - not include myname3 not on active sheet.

this macro return ones on activesheet (sheet4)