Question

考虑2个Excel文档：Excel文件A和Excel文件B。这些Excel文件中有工作表（文件A有A、B、c工作表，文件B有d、e、f工作表）。

我需要复制文件A、工作表A（依此类推）中的内容，并将其粘贴到我的试错工作表的第二页。我知道我需要为此做循环，但仅此而已。

我对这个编程很陌生，更不用说VBA了。

我想把表a中的内容复制到我的第二张纸上，把表b中的内容拷贝到表3中，以此类推。

Answer 1

一些让你开始的提示

（我不完全清楚你想要的一些细节，但这应该会让你开始）

首先打开两个工作簿，在其中一个工作簿中创建一个模块（这与代码中的哪一个无关），然后运行宏

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

Answer 2

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

Answer 3

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.

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”中的数据所需做的就是触发“数据”->；“全部刷新”。

友情链接