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

@urdearboy at first I thought the same, ...except it *is* all the context we need after all. — Mathieu Guindon, Jun 14 '18 at 01:10

score 2 · Accepted Answer · answered Jun 14 '18 at 01:03

2

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

"New Name" Excel dialog showing Scope field in a red circle

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

debug output of names("the name").Name values: Sheet1!SheetScopeTest1 and BookScopeTest1

answered Jun 14 '18 at 01:03

Mathieu Guindon

69,817
8
107
235

This was driving me crazy. (+1) – urdearboy Jun 14 '18 at 01:11
Thanks for that answer. However, I have multiple sheets all with the same name so I've had to sheet-scope them to avoid naming errors. It allows me to run the script on multiple sheets without changing too much of my code (apparently). I'm going to mark you response as the answer. I think I'm going to have to take the sheet name and replace it with 'nothing' in the string variable itself. Thank you! – Reece Edwards Jun 14 '18 at 03:25

Name Object Returning SheetName

1 Answers1