vba 将一个（关闭的）工作簿中的所有工作表复制到另一个“
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12029383/
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
Copy all sheets from one (closed) workbook to another "
提问by Alegro
I'm trying to copy all sheets from one book to another
我正在尝试将所有工作表从一本书复制到另一本书
Windows("test.xls").Sheets.Copy Before:=Workbooks(ThisWorkbook).Sheets("00")
Error - type mismatch !
"test.xls" is in the same folder as ActiveWorkBook.
Especially - is there a way to do this without opening "test.xls" ?
错误类型不匹配！
“test.xls”与 ActiveWorkBook 位于同一文件夹中。
特别是 - 有没有办法在不打开“test.xls”的情况下做到这一点？
回答by brettdj
You can open the closed workbook (opening in the background is far easier than working with closed books) and then copy all the sheets from Test.xlsto the specific part of the other book (ie before sheet 00) in one line
您可以打开关闭的工作簿（在后台打开比使用关闭的书籍容易得多），然后将所有工作表复制Test.xls到另一本书的特定部分（即工作表之前00）在一行中
The code below:
下面的代码：
- opens a closed workbook
c:\temp\Test.xls") - copies all the sheets before sheet
00in the Workbook containing the code - closes
Test.xls
- 打开关闭的工作簿
c:\temp\Test.xls") - 复制
00包含代码的工作簿中工作表之前的所有工作表 - 关闭
Test.xls
The code suppressed any alerts, code events in Test.xls and screenupdating
该代码在 Test.xls 和 screenupdating 中抑制了任何警报、代码事件
Sub CopyAll()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Set Wb1 = Workbooks.Open("c:\temp\Test.xls")
Set Wb2 = ThisWorkbook
Wb1.Sheets.Copy Before:=Wb2.Sheets("00")
Wb1.Close False
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub
回答by Tim van Zonneveld
Maybe i'm thinking to simple but if you want to copy all sheets, essentialy you are copying the whole spreadsheet file. Would'nt it be easier to programmatically save your 'old' workbook as a new name? You would create a whole new file with the same content.
也许我想简单但如果你想复制所有工作表，本质上你正在复制整个电子表格文件。以编程方式将“旧”工作簿保存为新名称不是更容易吗？您将创建一个具有相同内容的全新文件。
Maybe i'm missing something but it's another angle to look at it.
也许我错过了一些东西，但这是另一个角度来看待它。
