Find Named Ranges

[chiquita1]

Hello,

I'm fairly new to VBA with Excel, and i'm wondering if there's anyway i can search through a workbook to find Named Ranges a user may have created with excel. Using the Insert-> Name -> Define features provided on the Menu bar. I'm hoping to copy the named ranges from each worksheet onto the last worksheet within this workbook.

apparently this code does not work:

with activeworkbook
for each RangeName in .Names
if instr(RangeName, &quot;string1&quot <> 0 then (compare if the Range name is found within the worksheet/activeworkbook)
msgbox &quot;Range name found is &quot; & RangeName
endif
next
end with

If anyone can help i'd appreciate it, once again i'm search for named ranges already created by a user. The code above just gives my the actual cells positions if a cell has a value not a named range.

 

[xlbo]

This will loop thru all range names in the active workbook

For Each rn In ActiveWorkbook.Names
If InStr(rn.Name, &quot;!&quot = 0 Then
'user defined name
MsgBox rn.Name
Else
'excel auto name
End If
Next

To copy a rangename (ie the data in it) use

range(rn.address).copy destination:=Sheets(&quot;Sheetname&quot.range(&quot;Cell_Ref&quot Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[chiquita1]

Hey thanks!!!
i guess i was missing some code, i wasnt aware i could use the .Name property for my Variable RangeName, thanks again
RnC

&quot;I am not retreating, I am advancing in a different direction.&quot;
General Douglas MacArthur