Any quick way to remove external links from multiple named ranges?

Question

Is there an efficient way to remove external links from named ranges (or a way to prevent them entirely)?

I have a tracking spreadsheet that I use and deploy to multiple clients. They use the spreadsheets to track data and then submit the sheets to me every few months for review. Periodically I add a feature to a new tab on the spreadsheet and populate it to each client's copy as it comes in.

When I copy the new tab into the client spreadsheet, it brings with it external links back to the template. Those are easy enough to remove in formulas, but they always end up in 20 or so named ranges as well.

I end up manually slogging through 20 named ranges to individually remove each external link. This is problematic because Word's named ranges interface is cramped and lops the formula off after a few hundred characters. It only takes a couple of external links to make even a mid-sized formula quite long, meaning that the formula gets truncated and I have to recreate the formula entirely in order to remove the external links.

Is there a more efficient way to do this? Either a way to prevent named ranges from bringing in external links, or a way to quickly remove the links from multiple named ranges?

Answer 1

Have you tried making a copy of the tab and then deleting the original rather than moving the tab? right click on the tab and "Move or Copy" then ensure the "create a copy" checkbox is checked and select the book you want to copy to from the drop-down, this should prevent any links between the sheets named ranges.

For the named ranges you can try this code:

Option Explicit

Sub NmChnger()
Dim Nm As Name
Dim Strt As Long, Finish As Long
Dim MyStr As String, MyMid As String

For Each Nm In Names
If InStr(Nm, "[") > 0 Then
MyStr = Nm
Strt = InStr(Nm, "[")
Finish = InStr(Nm, "]")
MyMid = Mid(Nm, Strt, Finish - Strt + 1)
Nm.RefersTo = Application.WorksheetFunction.Substitute(Nm, MyMid, vbNullString)
Debug.Print MyStr & " was changed to " & Nm
End If
Next Nm

End Sub

ALT + F11 should open your developer editor, insert a module then enter the code in the module. Make the "immediate" window visible from the view menu then run the code by placing the cursor within the code and pressing the green play button (or linking it to a button in the workbook)

It will probably need some refinement but may just work as is, once you have run it copy the text from the immediate window and post back here if there is anything that needs modifying.

NB - I just saw your comment in your original question suggesting that your format is with 'link' rather than [link]. if this is the case you should just be able to change:

Strt = InStr(Nm, "[")
Finish = InStr(Nm, "]")

to

Strt = InStr(Nm, "'")
Finish = Len(Nm) - InStrRev(Nm, "'")

Not sure why your format is different to what I am getting, it may require further trouble shooting as you would then probably need to adjust + or - 1 somewhere.

Answer 2

Thanks for the answer above. I have improved it because the answer above did not remove the filename PATH. I also added option to change the filename, and then a check to continue. Thanks for all the answers people give.

Option Explicit
Sub NamedRangeFileNameRemover()
'
'   Appreciation and Acknowledgment to SUPERUSER site
'   https://superuser.com/questions/1563994/...
'           ...any-quick-way-to-remove-external-links-from-multiple-named-ranges
'       problem is it keeps asking for a new name...
'       when we want to remove filename link completely
'
'   Modified by Fr Gerard Conlan OMI - 28-Aug-2021
'   Removes or Replaces Filename Link
'   Prompts for an alternative Filename (or leave BLANK to remove)
'
Dim Nm As Name
Dim Strt As Long, Finish As Long
Dim MyStr As String, MyMid As String, MyReplace As String
Dim Title As String
'
Title = "Named Range: Filename Remove or Replace"
'
'eg. file name link      ='P:\Documents\[PAYMENT Template.xlsx]Accounts'!$A$1:$E$177
'    After removed       ='Accounts'!$A$1:$E$177
'    EXCEL Autocorrected =Accounts!$A$1:$E$177
'
For Each Nm In Names
    If InStr(Nm, "]") > 0 Then
    MyStr = Nm
        Strt = InStr(Nm, "[")   'This does not include the filename PATH
        Finish = InStr(Nm, "]")
'       MyReplace = vbNullString
            Strt = 2            'This includes the filename PATH
            If MyReplace = "" Then  'First Occurrence of a Named Range with a Filename
                MyMid = Mid(Nm, Strt + 1, Finish - Strt) 'excludes leading "'"
                MyReplace = InputBox("Enter New Filename or " & vbCrLf & _
                                      "leave blank to remove the filenames completely" & vbCrLf & _
                                      vbCrLf & "Current Filename= " & vbCrLf & vbCrLf & _
                                      MyMid, _
                                 Title, _
                                     "")
                If MsgBox("Continue: Are you Sure?" & vbCrLf & vbCrLf & _
                           MyReplace, _
                          vbOKCancel, Title) = vbCancel _
                          Then End
                MyReplace = "'" & MyReplace
            End If
            MyMid = Mid(Nm, Strt, Finish - Strt + 1)
        Nm.RefersTo = Application.WorksheetFunction.Substitute(Nm, MyMid, MyReplace)
        Debug.Print MyStr & " was changed to " & Nm
    End If
Next Nm
'
End Sub