拆分工作簿后如何移除命名范围中原工作簿的引用？

Hey there! Let's work through this issue you're facing with named ranges retaining references to your original workbook after splitting it. I'll break down where your VBA code went wrong, share a corrected version, and also cover manual fixes plus tips for making OFFSET-based ranges work in VBA.

First, Why Your Original Code Failed #

Your initial VBA attempt had a syntax issue with how you tried to use Replace. The Names object doesn't have a Replace method that works like that—instead, you need to:

1. Grab the current reference string from the named range's RefersTo property
2. Use VBA's built-in Replace function to strip out the original workbook reference
3. Assign the cleaned-up string back to the RefersTo property

Corrected VBA Code #

Here's a revised script that should do the trick. I've added comments to explain each step:

Sub FixNamedRangeReferences()
    Dim all_names As Variant, n As Variant
    Dim originalWBMarker As String
    Dim updatedReference As String
    
    ' List of named ranges to fix
    all_names = Array("1stNamedRange", "2ndNamedRange", "3rdNamedRange")
    ' The exact workbook reference string to remove (adjust if your path is full, e.g., 'C:\Docs\[Original_workbook.xlsm]')
    originalWBMarker = "[Original_workbook.xlsm]"
    
    For Each n In all_names
        ' Get the current reference text of the named range
        updatedReference = ThisWorkbook.Names(n).RefersTo
        
        ' Strip out the original workbook reference
        updatedReference = Replace(updatedReference, originalWBMarker, "")
        
        ' Optional: If your reference included a full file path (like 'C:\Files\[Original_workbook.xlsm]'), uncomment below
        ' updatedReference = Replace(updatedReference, "'C:\Files\", "'")
        
        ' Save the cleaned reference back to the named range
        ThisWorkbook.Names(n).RefersTo = updatedReference
    Next n
End Sub

Handling OFFSET-Based Named Ranges in VBA #

If you tried recreating named ranges with OFFSET and it didn't work, the issue is likely incorrect syntax when assigning the formula. Make sure your RefersTo string is exactly what you'd type manually in the name manager. For example:

' Create a dynamic OFFSET-based named range
ThisWorkbook.Names.Add _
    Name:="DynamicDataRange", _
    RefersTo:="=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)"

• If your worksheet has spaces or special characters, wrap the name in single quotes: 'Sales Report'!$A$1
• Double-check that all cell references and function arguments match what works manually in Excel.

Manual Fix (For Small Numbers of Named Ranges) #

If you don't want to use VBA, you can update references directly:

1. Open your new workbook and press Ctrl + F3 to open the Name Manager
2. Select a named range you want to fix
3. In the Refers to field, delete the original workbook reference (e.g., [Original_workbook.xlsm])
4. Press Enter to save the change
5. Repeat for all affected named ranges

Pro Tip #

Before making changes, close the original workbook—Excel sometimes keeps external references alive if the source file is open, which can make replacements less reliable.

内容的提问来源于stack exchange，提问作者Thijs O.