If you have two workbooks, or more, and you want to merge or consolidate those workbooks into one, then you may find it a tedious task to move the worksheets manually.

The following macro reads two workbooks and copy all the worksheets into a new consolidated workbook. If you have more than two workbooks as input then you can modify the macro accordingly.

Sub MergeWorkbooks()
‘**********************************************************
‘ Merge worksheets from two workbooks into a new workbook *
‘**********************************************************
Dim OldBook1 As Workbook
Dim OldBook2 As Workbook
Dim NewBook As Workbook
Dim wSheet As Worksheet
‘ Stop screen flicker and speed up the execution of the macro
Application.ScreenUpdating = False
‘ Create a new workbook ( the destination for merging the old ones)
Workbooks.Add
Set NewBook = ActiveWorkbook
‘ Assign the old workbooks (must be open before running the macro)
Set OldBook1 = Workbooks(“TestBook1.xls”) ‘ Put the required file name here
Set OldBook2 = Workbooks(“TestBook2.xls”) ‘ Put the required file name here
‘ Loop thru the first workbook an copy its worksheets to the destination workbook
For Each wSheet In OldBook1.Worksheets
wSheet.Copy After:=NewBook.Worksheets(NewBook.Worksheets.Count)
Next
‘ Loop thru the second workbook an copy its worksheets to the destination workbook
For Each wSheet In OldBook2.Worksheets
wSheet.Copy After:=NewBook.Worksheets(NewBook.Worksheets.Count)
Next
‘ Save the destination workbook
NewBook.SaveAs Filename:=”C:/TestMerg2.xls” ‘ Put the required path and file name here
‘Set screen updating back to normal
Application.ScreenUpdating = True
End Sub

This code is also available in the Downloads page.

Copy the code to a new module in Visual Basic Editor, then run it like any other VBA macro.

Suggested Post:
How to write a VBA macro

Applies to: Excel 2003