用于复制包含从源工作簿到目标工作簿的命名范围的工作表的 VBA 代码

VBA Code to Copy worksheets containing named ranges from source to destination workbook

提问by ansh

I have 2 workbooks. A Source workbook and a destination workbook. They are completely same except for 1 worksheet which has the same name in both but different data (both contain around 30 sheets). What i wanted was to Copy the rest of the identical worksheets from source workbook to destination workbook leaving that 1 worksheet which defers in data.

我有2本工作簿。源工作簿和目标工作簿。它们完全相同，除了 1 个工作表在两个但不同的数据中具有相同的名称（都包含大约 30 个工作表）。我想要的是将其余相同的工作表从源工作簿复制到目标工作簿，留下 1 个延迟数据的工作表。

Basically the identical worksheets present in the destination workbook should be replaced with the ones from the source workbook. The worksheets contain formulas and named ranges. I was successfully able to write the VBA Code to copy the worksheets. But since the named ranges have a workbook scope. The named ranges still refer to locations in the source workbooks. So i get 2 named ranges with the same name. Something like:

基本上，目标工作簿中存在的相同工作表应替换为源工作簿中的工作表。工作表包含公式和命名区域。我成功地编写了 VBA 代码来复制工作表。但是由于命名范围具有工作簿范围。命名范围仍然引用源工作簿中的位置。所以我得到了 2 个同名的命名范围。就像是：

'The one already present in the destination workbook (from the worksheet which was replaced)
Name=VaccStart , Refers To =Sheet2!$A 
'The one due to the copied worksheet.
Name=VaccStart , Refers To =[C:\Users\.....\Source.xls]Sheet2!$A 

I want the named ranges to refer to the destination workbook and not the source workbook when i copy them. Since all the sheets in both the workbooks are same and i am just replacing them.

我希望命名范围在我复制它们时引用目标工作簿而不是源工作簿。由于两个工作簿中的所有工作表都相同，我只是替换它们。

采纳答案by brettdj

One easy way to get around inadvertent link creation when moving from a source to destination workbook is to relink the destination workbook from Source to itself

从源工作簿移动到目标工作簿时，避免无意中创建链接的一种简单方法是将目标工作簿从源重新链接到自身

Screenshot for xl2010

xl2010 的截图

• Edit .... Links
• 'Change Source" and pick the current file as the new source

• 编辑 .... 链接
• “更改源”并选择当前文件作为新源

回答by Rachel Hettinger

This will modify the named ranges to remove the external file reference:

这将修改命名范围以删除外部文件引用：

Sub ResetNamedRanges()
    Dim nm As Name
    Dim sRefersTo As String
    Dim iLeft As Integer
    Dim iRight As Integer

    For Each nm In ActiveWorkbook.Names
        sRefersTo = nm.RefersTo
        iLeft = InStr(sRefersTo, "[")
        iRight = InStr(sRefersTo, "]")
        If iLeft > 1 And iRight > 0 Then
            sRefersTo = Left$(sRefersTo, iLeft - 1) & Mid$(sRefersTo, iRight + 1)
            nm.RefersTo = sRefersTo
        End If
    Next nm
End Sub