VBA代码：将包含命名区域的工作表从源工作簿复制到目标工作簿

Question

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

基本上，目标工作簿中存在的相同工作表应该被来自源工作簿的工作表替换。这些工作表包含公式和命名范围。我成功地编写了VBA代码来复制工作表。但由于命名范围具有工作簿范围，因此命名范围仍然引用源工作簿中的位置。所以我得到了两个具有相同名称的命名范围，类似于：

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

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

- ansh

---

当你复制工作表时，你的代码是什么？ - Jon49

SourceWorkBk.Worksheets(i).Copy Before:=DestWorkBk.Worksheets(myindex) - ansh

Answer 1

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

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

- Rachel Hettinger

---

嗨，这个很好用Rachel。但是经过复制大约30张表格之后，我最终得到了多个重复命名的同名范围。我的想法是在复制工作表后从目标中删除所有命名范围，然后将所有源名称复制并粘贴到目标中。告诉我这是否有意义。 - ansh

For Each nm In DestWorkBk.Names nm.Delete Next For Each nm In SourceWorkBk.Names DestWorkBk.Names.Add Name:=nm.Name, RefersTo:=nm.RefersTo Next - ansh

我有点不清楚你在目标工作簿中是否有两组命名区域（源工作簿和原始目标工作簿），还是只有一组，而引用指向错误的文件。我建议你尝试@brettdj的建议，看看是否有效。首先从目标文件中删除命名区域可能会起作用...也可以尝试这样做。 - Rachel Hettinger

Answer 2

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

适用于xl2010的屏幕截图

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

- brettdj

---

我的整个过程将被自动化，因为它将在大约200个工作簿的循环中执行相同的操作。所以不需要手工操作。您能告诉我这方面的VBA代码吗？ 另外，我刚刚发现除了命名区域之外，公式还在链接源工作簿。我该怎么解决呢？ - ansh

这个代码运行得非常好。这是我使用的代码： DestWorkBk.ChangeLink Name:=SourcePath & "\" & SourceFileName _ , NewName:=DestPath & "\" & DestFileName, Type:=xlExcelLinks现在有没有一种方法可以删除重复的命名区域？ - ansh

是的 - 您可以运行此代码，然后使用字典对象查找相同的拼接名称和工作表地址来进一步编写代码。我建议这是一个新问题 :) - brettdj