vba 将工作表从一个 Excel 文档复制到另一个
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6195566/
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
copying worksheets from one Excel document to another
提问by serene
Consider 2 Excel documents: Excel file A and Excel file B. These Excel files have worksheets inside them (file A has a,b,c worksheets and file B has d,e,f worksheets) .
考虑 2 个 Excel 文档：Excel 文件 A 和 Excel 文件 B。这些 Excel 文件中有工作表（文件 A 有 a、b、c 工作表，文件 B 有 d、e、f 工作表）。
I need to copy whatever is in file A, sheet a(and so on) and paste it to the 2nd sheet of my Trial and error worksheet. I know I need to do looping for this, but that's it.
我需要复制文件 A、工作表 a（等等）中的任何内容并将其粘贴到我的试验和错误工作表的第二张工作表中。我知道我需要为此进行循环，但仅此而已。
I am very new to this programming, let alone VBA.
我对这个编程很陌生，更不用说VBA了。
I want to copy whatever is in sheet a, to my 2nd sheet, and whatever is in sheet b, is copied in sheet 3, and so on.
我想将工作表 a 中的任何内容复制到我的第二张工作表中，并将工作表 b 中的任何内容复制到工作表 3 中，依此类推。
回答by chris neilsen
Some hints to get you started
帮助您入门的一些提示
(I'm not entirely clear on some of the details you want, but this should get you started)
（我不完全清楚你想要的一些细节，但这应该让你开始）
First open both workbooks, create a module in one of them (doesn't matter to this code which one) then run the macro
首先打开两个工作簿，在其中一个中创建一个模块（与此代码哪个无关紧要）然后运行宏
Option Explicit ' at top of module - forces explicit declaration of variables,
'a good thing particularly while learning
Sub CopySheets()
Dim wbFileA As Workbook
Dim wbFileB As Workbook
Dim sh As Worksheet
Dim shCopAfter As Worksheet
' Point to the workbooks
Set wbFileA = Application.Workbooks("NameOfFileA.xls")
Set wbFileB = Application.Workbooks("NameOfFileB.xls")
' Set pointer to first sheet in FileB
Set shCopAfter = wbFileB.Sheets(1)
' loop through the sheets in FileA
For Each sh In wbFileA.Sheets
' Copy sheet to FileB
sh.Copy After:=shCopAfter
' If last sheet in book then set shCopyAfter to last sheet
If ActiveSheet.Index >= wbFileB.Sheets.Count Then
Set shCopAfter = ActiveSheet
Else
' Else set shCopyAfter to the one after the one just copied
Set shCopAfter = wbFileB.Sheets(ActiveSheet.Index + 1)
End If
Next
End Sub
回答by Tim Williams
Dim x as Integer
Dim wbA as Workbook, wbB as Workbook
Set wbA = Workbooks("FileA")
Set wbB = Workbooks("FileB")
For x=1 to wbA.Sheets.Count
wbA.sheets(x).Copy After:=wbB.sheets((2*x)-1)
Next x
回答by user7515079
Well, eight years after this question has been originally asked, Power Query has been integrated into Excel and comes to help. From your destination workbook (“B”) you can load the source worksheets from “A” following these steps.
好吧，在最初提出这个问题八年后，Power Query 已集成到 Excel 中并提供帮助。从您的目标工作簿 (“B”)，您可以按照以下步骤从“A”加载源工作表。
- With workbook “B” as the active workbook go to “Data” -> “Get Data” -> “From File” -> “From Workbook” and select your source workbook “A” in the file browser.
- The Power Query Navigator dialog will appear.
- Select the table/worksheet you want to copy and press “Load” (“Transform Data” can be used to fine-tune the import).
- The source worksheet will be loaded.
- Repeat steps 1. to 4. for every worksheet you want to copy.
- 将工作簿“B”作为活动工作簿，转到“数据”->“获取数据”->“来自文件”->“来自工作簿”，然后在文件浏览器中选择源工作簿“A”。
- 将出现 Power Query 导航器对话框。
- 选择要复制的表/工作表，然后按“加载”（“转换数据”可用于微调导入）。
- 将加载源工作表。
- 对要复制的每个工作表重复步骤 1. 到 4.。
The benefit of this procedure is that, in case the source data changes, all you have to do to refresh the data in the destination “B” is to trigger “Data” -> “Refresh All”.
这个过程的好处是，如果源数据发生变化，刷新目标“B”中的数据所要做的就是触发“数据”->“全部刷新”。
