Convert named range scope from Worksheet to Workbook by overwriting only works for some named ranges

0
votes

I have hacked together code (from a couple of sources, references at the bottom) that programmatically will take worksheet scoped named ranges and convert them to workbook named ranges. However my code only works for some named ranges and not others, and I can't figure out why.

The reason I'm having to do this is because I had to delete two tabs (one of the tabs containing _T and the other _X) from the original source and copy in the duplicates of these tabs from another source. This leaves me with workbook scoped named ranges of #REF!#REF and worksheet named ranges that have the ranges I want but I need them Workbook scoped.

See code below

If I run this code looking for "_T", it works perfectly. All workbook named ranges starting with _T that were #REF!#REF now have the correct range and their counterpart worksheet named ranges are deleted. HOWEVER, if I run this looking for "_X", the workbook named range goes unchanged. I'm stumped. I have even tried a different approach where I manually delete all of the current workbook named ranges starting with _X, and then programmatically try to Add them using ActiveWorkbook.Names.Add Name:=newNm,RefersTo:=nm.RefersTo which also does nothing (doesn't even add a new record).

Thanks in advance for the help.

Sub WStoWBscope()

Dim nm As Name, Ans As Integer, newNm As String, fltr As String

fltr = "_X" 'search string


For Each nm In ActiveWorkbook.Names                'look at all named ranges within the current workbook
    If nm.Name Like "X!*" Then                     'looks for worksheet scoped named range that has the correct range
        If InStr(1, nm.Name, fltr) > 0 Then
            newNm = Replace(nm.Name, "X!", "")     'save name of existing workbook named range
            Range(nm.RefersTo).Name = newNm        'overwrite workbook named range with proper range
            nm.Delete                              'deletes worksheet named range
        End If
    End If
Next nm
End Sub

VBA to Convert Named Ranges Workbook to Worksheet Scope VBA to change the scope of named ranges from worksheet level to workbook

2
excelvbanamed-ranges
If nm.Name Like "X!*" only matches on names which begin with "X", so you will not find "_X" in those names (at least not at the beginning of the name) – Tim Williams
Understood, but that's actually not the issue. What I was saying is I'm looking for named ranges that start with _X. However, the entire entry under the nm.Names is the Sheet+[named range]. The InStr() portion of the code is searching for named range and then in the variable newNm, I'm removing the sheet from the full name. As I said, the code works fine in cases where I'm looking for _T (where I'm also replacing the X! with T!) – swadejax
Might be useful to debug.print a few examples of the problem names and include them in your post. – Tim Williams

2 Answers

1
votes

Try this:

Sub ConvertWorksheetNamedRangesToWorkbookNamedRanges()
    Dim nName As Name
    'Loop Through each named Range
    For Each nName In ActiveWorkbook.Names
        'Is Name scoped at the Workbook level?
        If TypeOf nName.Parent Is Workbook Then

        End If
        'Is Name scoped at the Worksheet level?
        If TypeOf nName.Parent Is Worksheet Then
            ' If nm.Name Like "X!*" Then .....
            ' Do the filtering you need
            ' ....
                Dim sName As String
                sName = nName.Name 'Save the name of the name
                Dim rngName As Range
                Set rngName = Range(nName) ' Save the range of the name
                nName.delete    ' Delete the name
                'Create a new one on workbook scope
                ThisWorkbook.Names.Add Name:=sName, RefersToR1C1:="=" & rngName.Address(ReferenceStyle:=xlR1C1)
            ' End If
        End If
    Next nName
End Sub
0
votes

Just for documentation purposes (don't upvote this, upvote Viktor's answer) here is what the final version of the code looks like:

Dim nm As Name, Ans As Integer, newNm As String, fltr As String, rngName As Range

'Filter named ranges that contain specific phrase
fltr = "_X"

'Search for all names in the workbook
For Each nm In ActiveWorkbook.Names
    'Search within those named ranges by those that have a specific worksheet scope
    If nm.Name Like "X!*" Then
        'Search for the named ranges of a type set by your filter (fltr)
        If InStr(1, nm.Name, fltr) > 0 Then
            'Take the full name [Scope]+[named range] and remove the scope
            newNm = Replace(nm.Name, "X!", "")
            'save the original range used by the worksheet-scoped named range 
            Set rngName = Range(nm)
            'delete the worksheet-scoped named range
            nm.Delete    
            'Create/Overwrite a workbook-scoped named range (this does overwrite any workbook-scoped named ranges that are the same name with #REF!#REF )
            ThisWorkbook.Names.Add Name:=newNm, RefersToR1C1:="=" & rngName.Address(ReferenceStyle:=xlR1C1)
        End If
    End If
Next nm
End Sub