How to Merge Multiple Workbooks to One Workbook via Excel VBA

When you need to collect information from other people, you will receive multiple Excel workbooks. And in this article, we will introduce the methods to merge multiple workbooks by using Excel macro.

When you have received multiple workbooks, you will find it hard to manage those files. All the information is separate in those different files. In the image below, you can see that there are several workbooks.Multiple Workbook

In each file, there are the sales volumes of this month. Now you need to input the data and information into one workbook. Instead of manually copy and paste those files, now you can use the methods below to finish this task quickly.

Merge Multiple Workbooks

  1. Now lunch a new Excel workbook.
  2. And then input the row header into this workbook.Row Header
  3. If you have already added the tab “Developer” in the ribbon, you can jump to step 6 directly. And in this step, right click any of a tab in the ribbon.
  4. Next click the option “Customize Ribbon” in the sub menu.Customize Ribbon
  5. In the “Excel Option” window, check the tab “Developer” in the “Main Tabs” area.
  6. And then click the button “OK” in this window. Thus, you have added the tab “Developer” in the toolbar.Developer
  7. Now click the tab “Developer” in the ribbon.
  8. And then click the button “Visual Basic” in the toolbar. Therefore, you will open the Visual Basic editor.Visual Basic

To replace those complex steps, you can directly press the shortcut keys “Alt +F11” on the keyboard.

  1. In this step, right click in the VBA project.
  2. And then in the new menu, move your cursor on the option “Insert”.
  3. After that, choose the option “Module” in the sub menu. Hence, you have inserted a new module into the workbook.Insert Module
  4. Now copy the following codes into the new module.
Sub MergeWorkbooks()

Dim TarFolder As String, TarFile As String, i As Integer

Application.ScreenUpdating = False

    i = 2

    TarFolder = "C:\Users\ Desktop\ Sales Volume"

    TarFile = Dir(TarFolder & "\*.xls")

    Do While TarFile <> ""

        Workbooks.Open Filename:=TarFolder & "\" & TarFile

        ActiveWorkbook.Sheets("Sheet1").Range("A2:C2").Copy _

Workbooks(1).Sheets("Sheet1").Range("A" & i)

        Application.CutCopyMode = False

        ActiveWorkbook.Close

        i = i + 1

        TarFile = Dir

    Loop

End Sub

In this macro, you need to change the path according to your actual path. And you also need to change the range according to the actual worksheet.

  1. Next click the button “Run Sub” in the toolbar or press the button “F5” on the keyboard to run this macro.
  2. And then switch to the workbook. You can see that all the information has already appear in the worksheet.Result

You can see that using VBA macro can help you finish this task quickly. And next you can save this file to a designated path. But you also need to pay attention that this workbook contains macro. When you save this workbook, you need to choose the file type according to your need.

Find a Powerful Data Recovery Company

When you are working on an Excel worksheet, you will sometimes meet with Excel corruption. What’s worse, this corruption will cause severe result. Of course the first thing you need to do is trying to fix this file. Otherwise all the other files will get affected. If you have no knowledge about why Excel corrupts, do not try to fix it by yourself. Some incorrect manipulation will make you lose access to this file forever. You can find a powerful data recovery company for help. And at the same time, using a potent tool to repair Excel file error is also very helpful. Thus, you can make sure that you will not lose your data and information.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair docx document problem and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.