Name Object Returning SheetName

Question

I am trying to loop through all my Named Ranges and set them as a string if they contain a certain substring using a wildcard operator and LIKE. Finding the correct Named Range is working as it should.

However, when I set the string to the name of the range, I am getting the full sheet name and not just the Range Name.

"'SheetName'!Range Name" should be "Range Name". When using the string later on in the code, it is producing errors because of the sheetname. I could trim and replace, but thought there might be a more direct method to solving the problem.

Dim nm as Name
Dim CurrentRange as String   

If nm.Name Like "*Name" Then
    CurrentRange = nm.Name

Answer 1

The Name is worksheet-scoped. Delete it and make a new one with the same RefersTo range, making sure it's at Workbook scope:

Sheet-scoped names have the sheet's name in their... name.