I need to loop through all named ranges in my sheet.
I am currently doing this:
For Each nm In ActiveWorkbook.Names The problem is that this sheet has names with global scope and with sheet scope.
This loop only gets the global scope.
I've tried:
For Each nm In Activeworkbook.Sheets(1).Names But couldn't make it work. This loop also only gets those named ranges with global scope. Ideas?
I know the best solution would be to change the scope of the name, but I can't do it.
Could make it to work today. To post here, I made my problem much more simple, but this wasn't a good idea.
What I really do is to import some values from a sheet (selected with application.getopenfilename and opened with workbooks.open). So I loop through all names in this "imported" sheet and import the values of those ranges to ranges with the same name in my original sheet.
for each nm in thisworkbook.names if left(nm.name, 5) = "campo" then 'here I make my copy end if next nm Turns out that when you have a name with sheet scope, nm.name returns something like this:
nameOfSheet!nameOfField So I could never get in to that if. To solve my problem I used the following line. Thank you all for trying to help me.
currentName = Mid(nm.Name, InStr(1, nm.Name, "!") + 1) Dim intCounter As Integer Dim nmTemp As Name For intCounter = 1 To ActiveWorkbook.Names.Count MsgBox ActiveWorkbook.Names(intCounter) Next intCounter 
Dim Obj as Variant For Each Obj In ThisWorkbook.Names If InStr(Obj.Name, "ExternalData") > 0 Then Obj.Delete End If Next Obj Refining hend's answer, we can use InStr to determine if a string is within the name range's name, which works for both local and global scope name ranges
