change scope of named ranges from local to global

I have a worksheet with about a dozen named cells/ranges and several formula that use those named cells.

I need to replicate that sheet several times in the same workbook. If I simply make a copy of the worksheet, all range names are converted to local scope. I have VBA code to rename the ranges on the copied sheets in order to make every name globally unique, however, I then need to change the scope to global on each sheet.

I've seen several solutions here for going the other direction -- from global to local -- that use VBA to delete and recreate each range name. I don't want to do that, however, because of the formulas that refer to each named range.

Is there a way to switch my names from local to global while preserving my formula? I think I can work around the problem by copying my worksheet to a new workbook to preserve the global scope, renaming ranges, and then copying back, but I would prefer a solution that works within the original workbook.
LVL 31
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Sub MakeGlobal()
Dim nr As Name
Dim strName As String
Dim strRefersTo As String

For Each nr In ActiveWorkbook.Names
    If Not nr.RefersToRange Is Nothing Then
        With nr.RefersToRange
            strName = Split(nr.Name, "!")(1)
            strRefersTo = nr.RefersToR1C1
            ' Remove the named range
            ' Add it back as global
            ActiveWorkbook.Names.Add Name:=strName, RefersToR1C1:=strRefersTo
        End With
    End If
End Sub

Open in new window

Ejgil HedegaardCommented:
If the names in the sheet you copy are global, they remain global, and new local names are made on the copied sheet.
The formulas on the copied sheet refer to the local names, but if you delete the local names, all formula references will point to the global names.
So no need to delete and recreate.
This macro deletes worksheet names.
Sub DeleteLocalNames()
Dim ws As Worksheet
Dim nr As Name

For Each ws In ActiveWorkbook.Worksheets
    For Each nr In ws.Names
        If Not nr.RefersToRange Is Nothing Then
        End If
    Next nr
Next ws
End Sub

Open in new window

Scott HelmersVisio Consultant, Trainer, Author, and DeveloperAuthor Commented:
Thanks, but that's not quite what I need. I didn't explain sufficiently. The formulas in each worksheet refer only to names within that worksheet. However, I need the names in each sheet to be global so I can refer to them in formulas on another worksheet. Think of what I want this way:

sheet1:  s1_name1,  s1_name2,  s1_sum = s1_name1 + s1_name2
sheet2:  s2_name1,  s2_name2,  s2_sum = s2_name1 + s2_name2
sheet3:  s3_name1,  s3_name2,  s3_sum = s3_name1 + s3_name2
sheet4:  s4_total = s1_sum + s2_sum + s3_sum
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Martin LissOlder than dirtCommented:
Can you attach your actual workbook or a small sample that can be used for my testing?
Ejgil HedegaardCommented:
That is not making the sheet names global.
They are still local.
But you can reference a name on another sheet by using the sheet name in the reference.

Add ' before the sheet name, and '! after like this

sheet4:  s4_total = 'Sheet1'!s1_sum + 'Sheet2'!s2_sum + 'Sheet3'!s3_sum

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperAuthor Commented:
True, Ejgil, I can include the sheet name in the cell reference... perhaps that is a better solution than maintaining five sets of unique page names. I was trying to keep the cross-sheet references simpler by having unique names on every page, but your suggestion may be more practical.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.