copying worksheets from one Excel document to another

Question

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) .

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.

I am very new to this programming, let alone 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.

Answer 1

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

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.

1. 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.
2. The Power Query Navigator dialog will appear.
3. Select the table/worksheet you want to copy and press “Load” (“Transform Data” can be used to fine-tune the import).
4. The source worksheet will be loaded.
5. Repeat steps 1. to 4. for every worksheet you want to copy.

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”.