Merge open workbooks into one in Excel

Combining data from several Excel workbooks into one is a typical task when consolidating reports, collecting data from colleagues or analyzing exports from several sources. Most often, the files are already open in Excel, have a similar structure, and their data needs to be collected in one place.

There are two approaches: manual merging with Excel tools and automation with VBA or specialized tools. For a one-time task, copying is enough, but for regular work, mistakes and lost time pile up quickly without automation.

Typical scenario

Several workbooks are open, each with one or more sheets of data with the same structure. You need to go through all of them, take the data from the right sheets and add it one after another to one workbook, usually to the end of a table.

This scales badly by hand. Even with five or six workbooks, the risk grows of skipping a sheet, pasting data in the wrong place or breaking the table structure.

Move or copy whole sheets

If you need the sheets in one workbook rather than the data in one table, Excel can do it without code: select the sheet tabs in a source workbook, right-click → Move or Copy, choose the target workbook in To book, check Create a copy if needed, and click OK. Repeat for each workbook.

VBA code to merge open workbooks

Below is a simplified VBA procedure that collects the data from the first sheet of all open workbooks (except the one with the code and the Personal Macro Workbook) and appends it to the first sheet of the current workbook:

Sub MergeOpenWorkbooks()
    Dim wb As Workbook, target As Worksheet, src As Range, nextRow As Long
    Set target = ThisWorkbook.Worksheets(1)
    For Each wb In Application.Workbooks
        If wb.Name <> ThisWorkbook.Name And Not wb.Name Like "PERSONAL*" Then
            Set src = wb.Worksheets(1).Range("A1").CurrentRegion
            nextRow = target.Cells(target.Rows.Count, 1).End(xlUp).Row + 1
            If nextRow = 2 And IsEmpty(target.Range("A1")) Then nextRow = 1
            src.Copy Destination:=target.Cells(nextRow, 1)
        End If
    Next wb
End Sub

The code shows the principle: go through the open workbooks, copy the data and paste it one block after another. In practice, such macros quickly get more complex: structure checks, skipping empty workbooks, choosing specific sheets, filtering rows and error handling. Note that the header row of each workbook is copied too.

You can use the code temporarily in a module of the current workbook, or store it in the Personal Macro Workbook to reuse it in any file.

Limitations of the VBA approach

Despite its flexibility, merging workbooks with VBA has practical limits. The code is tied to the data structure, is hard to move between files and needs macros enabled. And for most users, writing and maintaining such procedures is too complicated. If the file structure changes or the merge has to be done regularly, maintaining the code becomes a separate task with nothing to do with data analysis.

For files that keep arriving in a folder, Power Query (Data → Get Data → From File → From Folder) is a good built-in alternative, as shown in the article on how to merge CSV files.

Merge open workbooks with !SEMTools

For those who want to merge open workbooks quickly and safely without writing macros, !SEMTools has a dedicated tool. It merges all open workbooks into one in a few clicks, adding their content one after another. No VBA to understand, no macros to enable, no code to adapt for every new task.

This is especially handy when data regularly arrives as separate files: branch reports, system exports, scraping or export results.

What’s next

After merging all workbooks into one, you’ll usually need further consolidation and cleanup. For example, delete sheets with a certain name and combine the data from all sheets onto one sheet.

Summary

I hope you found what you were looking for on this page and were able to merge your workbooks. Move or Copy works for a few sheets, VBA or Power Query for regular tasks with a fixed structure, and the !SEMTools tool merges all open workbooks in a few clicks without code.