没有原始工作簿参考的 Excel VBA 复制表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24328526/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Copy Sheet without original workbook reference
提问by Erika
I have a routine that is copying various sheets from many workbooks and pasting these sheets in what I am calling a 'master' workbook (which contains the different sheets from the different workbooks).
我有一个例程，它从许多工作簿中复制各种工作表，并将这些工作表粘贴到我称之为“主”工作簿（其中包含来自不同工作簿的不同工作表）中。
The code works well, however it is also keeping a reference to the original sheet with the usual 'extra' for example =SUM([Book1]Sheet1!C13:G13)(to keep the example simple).
该代码运行良好，但它也保留了对原始工作表的引用，例如通常的“额外” =SUM([Book1]Sheet1!C13:G13)（以保持示例简单）。
The worksheets I am copying need to be copied in the exact same formatting where I am using the following command:
我正在复制的工作表需要以与我使用以下命令完全相同的格式进行复制：
Dim WS_Count As Integer
WS_Count = wb.Worksheets.Count
For c = 1 To WS_Count
If wb.Sheets(c).Name <> "TEST" Then
wb.Sheets(c).Copy Before:=master.Worksheets(master.Sheets.Count)
End If
Next
The Copying and merging of documents works very well however as I am also copying some summary sheets from the workbooks, these contain internal reference to sheets and I am encountering difficulties in copying without the original workbook reference [Book1]. I would not know the file name of the workbook while I am working on the master workbook because there are many source workbook documents.
文档的复制和合并效果很好，但是因为我也在从工作簿中复制一些摘要表，这些包含对工作表的内部参考，如果没有原始工作簿参考 [Book1]，我在复制时遇到了困难。我在处理主工作簿时不知道工作簿的文件名，因为有很多源工作簿文档。
My question is, Is there a way to copy a sheet with all of its formatting without copying the cell workbook reference?
我的问题是，有没有办法在不复制单元格工作簿引用的情况下复制具有所有格式的工作表？
I have also tried all variations of Paste / Paste Special however this either loses the worksheet formatting or still retains the other workbook reference.
我还尝试了粘贴/特殊粘贴的所有变体，但是这要么丢失了工作表格式，要么仍然保留了其他工作簿引用。
I wish to avoid having to find and replace any string that contains [... .xls] as its not an elegant solution. Any pointers in the right direction would be appreciated.
我希望避免查找和替换任何包含 [... .xls] 的字符串，因为它不是一个优雅的解决方案。任何指向正确方向的指针将不胜感激。
回答by MP24
If you copy all your sheets at once, the formula references will point to the copied sheets instead of the source sheets. You will achieve the same functionality as selecting multiple sheets using Shift with the help of the following code:
如果您一次复制所有工作表，公式引用将指向复制的工作表而不是源工作表。借助以下代码，您将获得与使用 Shift 选择多个工作表相同的功能：
wb.Worksheets(Array("Sheet1", "Sheet2")).Copy Before:=master.Worksheets(master.Worksheets.Count)
So if your worksheet names are fixed, you can replace the inside the Array()function call, else you would need to create an appropriate array inside your For c = 1 To WS_Countloop and call the copy code once afterwards:
因此，如果您的工作表名称是固定的，您可以替换内部Array()函数调用，否则您需要在For c = 1 To WS_Count循环中创建一个适当的数组，然后调用一次复制代码：
Dim ws() As String ' declare string array
ReDim ws(wb.Worksheets.Count) As String ' set size dynamically
Dim counter As Long ' running counter for ws array
counter = 1
For c = 1 to WS_Count
If wb.Worksheets(c).Name <> "TEST" Then
ws(counter) = wb.Worksheets(c).Name
counter = counter + 1
End If
Next
ReDim Preserve ws(counter) As String ' Strip of superfluous empty array entries (i.e. TEST sheets
wb.Worksheets(ws).Copy Before:=master.Worksheets(master.Worksheets.Count)
Note that this code is untested.
请注意，此代码未经测试。
