Managing and importing named ranges from another workbook

Question

I was looking at the code available here (https://stackoverflow.com/a/56868436) but clearly I could use some guidance.

Public Sub RescopeNamedRangesToWorkbookV2()
Dim wb As Workbook
Dim ws As Worksheet
Dim objNameWs As Name
Dim objNameWb As Name
Dim sWsName As String
Dim sWbName As String
Dim sRefersTo As String
Dim sObjName As String
Set wb = ActiveWorkbook
Set ws = ActiveSheet
sWsName = ws.Name
sWbName = wb.Name

'Loop through names in worksheet.
For Each objNameWs In ws.Names


'Check name is visble.
    If objNameWs.Visible = True Then
'Check name refers to a range on the active sheet.
        If InStr(1, objNameWs.RefersTo, sWsName, vbTextCompare) Then
            sRefersTo = objNameWs.RefersTo
            sObjName = objNameWs.Name
'Check name is scoped to the worksheet.
            If objNameWs.Parent.Name <> sWbName Then
'Delete the current name scoped to worksheet replacing with workbook scoped name.
                sObjName = Mid(sObjName, InStr(1, sObjName, "!") + 1, Len(sObjName))
'Check to see if there already is a Named Range with the same Name with the full workbook scope.
                For Each objNameWb In wb.Names
                    If sObjName = objNameWb.Name Then
                    MsgBox "There is already a Named range with ""Workbook scope"" named """ + sObjName + """. Change either Named Range names or delete one before running this Macro."
                    Exit Sub
                    End If
                Next objNameWb
                objNameWs.Delete
                wb.Names.Add Name:=sObjName, RefersTo:=sRefersTo
            End If
        End If
    End If
Next objNameWs
End Sub

My set-up, 2 excel files, WB1 and WB2 where WB1 is the one active. Both, the WB1 and WB2 have named ranges. All names are defined as global in their own WB. Both WB have names unique to the specific WB but also common names, ie. referring to country

The final goal I try to achieve is to be able to import specific sheets from WB2 into WB1 where:

The global names of WB1 are untouched
The imported common global names that are imported from WB2 are deleted/updated with the global name/range already available in WB1 (As far as I understand, if a common globalname is imported it’s scope becomes local to the imported worksheet.)
The imported unique names are imported/updated to a global range (I guess by default they are imported as global, nevertheless a check with possible correction would be preferred)

The reason I ask is because I have many specific calculation sheets, each specific to a certain topic. Each of them can be used as a standalone file. But when I have to calculate multiple topics for one project, I want the imported the specific calculation sheets from WB2 and have those sheets linked to or using the basic data already available in WB1 (such as Customer, country, specific rates, delivery times, ….) At the moment all calculation sheets are in one WB but this is becoming pretty slow

score 0 · Accepted Answer · answered Jul 07 '20 at 15:47

seems I was making it way to difficult

replacing:

MsgBox "There is already a Named range with ""Workbook scope"" named """ + sObjName + """. Change either Named Range names or delete one before running this Macro."
Exit Sub

With

sRefersTo = objNameWb.RefersTo

seems to do the trick

Additionally, like in my case, you might want to look for external referenced ranges and delete those as they would create double names (same name but linked to current and external file and both globally scoped)

Managing and importing named ranges from another workbook

1 Answers1