Loop through all named ranges in a Excel Sheet

Question

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.

Accepted Answer

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)
```

Loop through all named ranges in a Excel Sheet

Solution

Problem

Related problems