excel - 尝试复制整个工作簿，公式仍链接到旧工作簿

Question

我有以下代码可以使用 excel VBA 将模板工作簿中的工作表复制到新工作簿中:

Dim wkbSource As Workbook
Dim wkbDest As Workbook
Dim shtToCopy As Worksheet

'Opens Template file, and the new workbook.
            Set wkbSource = Workbooks.Open(MyPath & "Template.xlsx")
            Set wkbDest = Workbooks.Open(MyPath2 & "NewBook.xlsx")

            'Copys the "Approval sheet" Tab from the Template to the new workbook
            Set shtToCopy = wkbSource.Sheets(" Approval sheet")
            shtToCopy.Copy wkbDest.Sheets(1)

            'Copys the "ECL (1)" Tab from the Template to the new workbook
            Set shtToCopy = wkbSource.Sheets("ECL (1)")
            shtToCopy.Copy wkbDest.Sheets(2)

问题是从模板工作簿中复制的任何公式都将链接回模板文件中的单元格，而不是链接到新工作簿中的单元格。

我怎样才能解决这个问题？

Answer 1

您需要在一次操作中复制两张纸，如下所示

    wkbSource.Worksheets(Array(" Approval sheet", "ECL (1)")).Copy wkbDest.Sheets(2)