外部工作簿中的Excel复制工作表 [英] Excel Copy Worksheet from external WorkBook

问题描述

我正在做的是将工作表从其他工作簿复制到当前工作簿.我基本上是在做以下事情:

What I am doing is copying a sheet from a different workbook to my current workbook. I'm basically doing the following:

Delete the current Worksheet in the current Workbook

Open the external Workbook and Copy the Worksheet required

这一切都按预期工作，但其他工作表中的所有引用均丢失，并替换为#REF.

This all works as expected but all references in the other sheets are lost and replaced with #REF.

有没有可以避免这种情况的解决方法(查找和替换黑客除外).

Is there a workaround (other than find and replace hack) that can be done to avoid this.

此致

劳埃德

推荐答案

为什么不只复制工作表中包含的值而不是整个工作表对象?

Why don't you just copy the values contained in the sheet instead of the whole sheet object?

v = Workbooks("Book1").Worksheets("Sheet2").Range("A1:IV65536")
Workbooks("Book3").Worksheets("Sheet1").Range("A1:IV65536") = v

其中 v 是变体.或者，

Workbooks("Book3").Worksheets("Sheet1").Range("A1:IV65536") = _
    Workbooks("Book1").Worksheets("Sheet2").Range("A1:IV65536")

这需要花费几秒钟的时间，但是如果您将范围缩小到真正需要的时间会更快("A1:IV65536" 可能被夸大了...)

This takes a couple of seconds, but will be faster if you reduce the range to what you really need ("A1:IV65536" is presumably exaggerated...)

如果要复制的工作表中也有公式，而目标工作表中需要这些公式，那么这当然不会.从您的问题尚不清楚您到底要完成什么.

Of course this won't do if you also have formulas in the sheet you're copying and you need those formulas in the destination sheet. It isn't clear from your question what exactly you're trying to accomplish.

这篇关于外部工作簿中的Excel复制工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！