试图复制整个工作簿，公式仍链接到旧工作簿 [英] Trying to copy entire workbook, formulas still link to the old workbook

问题描述

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

I have the following code to copy sheets from a template workbook into a new workbook using 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)

问题在于，从模板工作簿中复制的所有公式都将链接回到模板文件中的单元格，而不是链接到新工作簿中的单元格.

The problem is that any formulas copied out of the template workbook will link back to cells in the template file, rather than linking to cells within the new workbook.

我该如何解决?

推荐答案

您需要像这样在同一操作中复制两个工作表

You need to copy both sheets in a single operatiuon, like this

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

这篇关于试图复制整个工作簿，公式仍链接到旧工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！