Copy multiple Excel workbooks into one workbook

Question

I am trying to copy multiple Excel workbooks into a single workbook with each of the copied workbooks to have their own Tab.

I do not know how to use VB, so I have tried many VB scripts I found using Google, but for one reason or another (error 9, error 91, nothing happens, all workbooks gets copied into one tab) I have not managed to get it done.

Answer 1

If you don't have a lot of workbooks, you can do it manually by following these instructions. Relevant excerpts:

• Right-click on the tab you want to move and choose move or copy
• Select the target book in the pull list
• Select the tab location where you would like it in the target book
• Click ok

If you have a lot of workbooks, you can automated it by following these instructions. Relevant excerpts:

• Put all the workbooks in the same directory and note the directory path
• Open the target workbook
• Click Developer -> Visual Basic
• In new window, click Insert -> Module

• Paste the following code:

  Sub GetSheets()
  Path = "<INSERT PATH TO DIRECTORY HERE>"
  Filename = Dir(Path & "*.xls*")
    Do While Filename <> ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
      For Each Sheet In ActiveWorkbook.Sheets
      Sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next Sheet
      Workbooks(Filename).Close
      Filename = Dir()
    Loop
  End Sub
  

• Replace the Path line with the full path to the directory with the workbooks

• Click the green arrow buttonto run the code and merge the workbooks.

Answer 2

This works -

    Sub CopyBooks()
    Application.ScreenUpdating = False
    Application.Calculation = xlManual

    Dim destinationWorkbook As Workbook
    Set destinationWorkbook = ThisWorkbook
    Dim sourceWorkbook As Workbook
    Dim sourceWorksheet As Worksheet
    Const path As String = "C:\your\path\"
    Dim file As Variant

    Dim currentSheets As Long
    currentSheets = destinationWorkbook.Sheets.Count

    file = Dir(path & "*.xl*")

    While file <> ""
        Set sourceWorkbook = Workbooks.Open(path & file)
            For Each sourceWorksheet In sourceWorkbook.Worksheets
                sourceWorksheet.Copy after:=destinationWorkbook.Worksheets(currentSheets)
                currentSheets = currentSheets + 1
            Next
            sourceWorkbook.Close savechanges:=False
            file = Dir
    Wend

    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    End Sub