trying to set global named range but local range e

Question

I'm trying to change the address that a named range refers to. There are two ranges in the workbook with the same name, one scoped to the workbook and the other scoped to SheetA. I'm using this code:

Sub changeNamedRangeAddress(bk As Workbook, rangeName As String, newRange As Range)
    bk.Names(rangeName).RefersTo = newRange
End Sub

When I look at the value for bk.Names(rangeName) in the Immediate window, it appears to be referencing the global version of that name, because the following returns true:

?typeof bk.Names(rangeName).Parent is Workbook

But after the sub runs, the locally scoped version's address has changed to that of newRange.address and the global one remains the same.

Is there anything else I can do to make sure that .RefersTo targets the global named range?

EDIT: The sheet that the locally scoped named range refers to is active when this script runs.

Answer 1

I solved this by activating another worksheet. So the code now looks like this:

Sub changeNamedRangeAddress(bk As Workbook, rangeName As String, newRange As Range)
    bk.Sheets("SheetB").Activate
    bk.Names(rangeName).RefersTo = newRange
End Sub

For some reason this allows .RefersTo to modify the global range instead of the one that points to SheetA. This seems like an odd way to solve the problem, though, so I'll wait to see if anyone comes up with anything better before accepting my own answer.

Answer 2

Here is a solution that is more functional if you have a few or several cases like this across your sheets (which I have seen), but won't make much of a difference with 1 or 2, except you'll have a more functional procedure in your library! :)

Option Explicit

Sub changeGlobalNamedRangeAddress(bk As Workbook, rangeName As String, newRange As Range)
'this sub only changes named range scoped to the workbook and ignores any ranges scoped to the worksheet with the same name.

Dim n As Name

For Each n In bk.Names

    If InStr(1, n.Name, rangeName) > 0 And InStr(1, n.NameLocal, "!") = 0 Then

        n.RefersTo = newRange
        Exit For

    End If

Next

End Sub