vba - 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