Excel – Copy multiple Excel workbooks into one workbook

microsoft excelvba

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.

Best Answer

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.
Related Question