VBA Code to Consolidate Excel Files into One Workbook

To consolidate matching Excel files into one worksheet, place the macro below in the workbook you want to use as the destination, then run it and select the folder containing the source files. It copies values from the first worksheet of each Excel file, keeps the first file’s header row, and adds a Source File column so you can trace each record.

What this macro consolidates

This example combines rows from multiple workbooks into a single worksheet named Consolidated. It is designed for files with the same column layout and a header row in row 1. For example, if each monthly workbook has columns for Date, Product, and Sales, the macro appends the records beneath one shared header rather than creating a separate worksheet for each month.

The macro reads the first worksheet in each source workbook. It copies cell values, not formulas or formatting, and writes the source workbook’s filename beside each imported row. It does not combine every sheet in every file, preserve charts, or reconcile different column arrangements. Those distinctions matter: “consolidate” can mean appending similar tables, copying sheets, or summarizing figures, and this code handles the first of those tasks.

VBA code to combine files

Save a copy of the destination workbook before running the macro. It clears the contents and formatting of an existing Consolidated worksheet, and macros cannot be undone with Ctrl+Z. The source workbooks are opened read-only and are not saved by the code.

Option Explicit

Sub ConsolidateExcelFiles()
    Dim folderPath As String
    Dim fileName As String
    Dim fullName As String
    Dim sourceFile As String
    Dim sourceBook As Workbook
    Dim sourceSheet As Worksheet
    Dim outputSheet As Worksheet
    Dim lastCell As Range
    Dim lastRow As Long
    Dim lastCol As Long
    Dim dataColumns As Long
    Dim nextRow As Long
    Dim rowsToCopy As Long
    Dim filesAdded As Long
    Dim rowsAdded As Long
    Dim foundOutput As Boolean
    Dim oldScreenUpdating As Boolean
    Dim oldEnableEvents As Boolean
    Dim errorText As String

    ' Ask for the folder containing the source workbooks.
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select the folder containing the Excel files"
        If .Show <> -1 Then Exit Sub
        folderPath = .SelectedItems(1)
    End With

    On Error GoTo CleanFail

    ' Find the destination sheet, or create it if needed.
    For Each outputSheet In ThisWorkbook.Worksheets
        If outputSheet.Name = "Consolidated" Then
            foundOutput = True
            Exit For
        End If
    Next outputSheet

    If Not foundOutput Then
        Set outputSheet = ThisWorkbook.Worksheets.Add( _
            After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        outputSheet.Name = "Consolidated"
    End If

    ' This intentionally replaces existing output on that sheet.
    outputSheet.Cells.Clear
    nextRow = 2
    oldScreenUpdating = Application.ScreenUpdating
    oldEnableEvents = Application.EnableEvents
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ' Match common Excel workbook extensions; ignore temporary lock files.
    fileName = Dir(folderPath & Application.PathSeparator & "*.xls*")
    Do While Len(fileName) > 0
        If Left$(fileName, 2) <> "~$" Then
            fullName = folderPath & Application.PathSeparator & fileName

            ' Do not read the destination workbook as a source file.
            If StrComp(fullName, ThisWorkbook.FullName, vbTextCompare) <> 0 Then
                Set sourceBook = Workbooks.Open( _
                    Filename:=fullName, UpdateLinks:=0, ReadOnly:=True, AddToMru:=False)
                Set sourceSheet = sourceBook.Worksheets(1)

                ' Find the last cell containing a value or formula.
                Set lastCell = sourceSheet.Cells.Find( _
                    What:="*", After:=sourceSheet.Cells(1, 1), _
                    LookIn:=xlFormulas, LookAt:=xlPart, _
                    SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)

                If Not lastCell Is Nothing Then
                    lastRow = lastCell.Row
                    Set lastCell = sourceSheet.Cells.Find( _
                        What:="*", After:=sourceSheet.Cells(1, 1), _
                        LookIn:=xlFormulas, LookAt:=xlPart, _
                        SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
                    lastCol = lastCell.Column

                    If dataColumns = 0 Then
                        dataColumns = lastCol
                        outputSheet.Cells(1, 1).Resize(1, dataColumns).Value2 = _
                            sourceSheet.Cells(1, 1).Resize(1, dataColumns).Value2
                        outputSheet.Cells(1, dataColumns + 1).Value = "Source File"
                    ElseIf lastCol <> dataColumns Then
                        Err.Raise vbObjectError + 1000, "ConsolidateExcelFiles", _
                            "A source file has a different number of used columns: " & fileName
                    End If

                    ' Append data rows, excluding each file's header row.
                    If lastRow > 1 Then
                        rowsToCopy = lastRow - 1
                        outputSheet.Cells(nextRow, 1).Resize(rowsToCopy, dataColumns).Value2 = _
                            sourceSheet.Cells(2, 1).Resize(rowsToCopy, dataColumns).Value2
                        sourceFile = fileName
                        outputSheet.Cells(nextRow, dataColumns + 1).Resize(rowsToCopy, 1).Value = sourceFile
                        nextRow = nextRow + rowsToCopy
                        rowsAdded = rowsAdded + rowsToCopy
                    End If
                    filesAdded = filesAdded + 1
                End If

                sourceBook.Close SaveChanges:=False
                Set sourceBook = Nothing
            End If
        End If
        fileName = Dir()
    Loop

    If filesAdded > 0 Then
        outputSheet.Rows(1).Font.Bold = True
        outputSheet.Range(outputSheet.Cells(1, 1), _
            outputSheet.Cells(nextRow - 1, dataColumns + 1)).AutoFilter
        outputSheet.Columns.AutoFit
    End If

    Application.ScreenUpdating = oldScreenUpdating
    Application.EnableEvents = oldEnableEvents
    MsgBox filesAdded & " source file(s) processed; " & rowsAdded & _
        " data row(s) copied to Consolidated.", vbInformation
    Exit Sub

CleanFail:
    errorText = Err.Description
    On Error Resume Next
    If Not sourceBook Is Nothing Then sourceBook.Close SaveChanges:=False
    Application.ScreenUpdating = oldScreenUpdating
    Application.EnableEvents = oldEnableEvents
    On Error GoTo 0
    MsgBox "The consolidation stopped: " & errorText, vbExclamation
End Sub

How to install and run the macro

Open the destination workbook in Excel for Microsoft 365 on Windows and save a backup copy. Save the working destination as an Excel Macro-Enabled Workbook (*.xlsm) if it is not already in that format.
Open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the complete code into the module, and save the workbook. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer.
Return to Excel and run the macro from Developer > Macros. Select ConsolidateExcelFiles and choose Run, then select the folder containing the files. The macro creates a Consolidated sheet if one does not exist.
Check the result, especially the headers, row count, and Source File values, before using the combined data. Save the destination workbook after confirming the output is correct.

On Mac, Excel desktop supports VBA, but the Visual Basic Editor shortcut can depend on the keyboard and function-key settings; use Tools > Macro > Visual Basic Editor if needed. Macro-related menu locations can vary slightly by Excel for Mac release. Excel for the web cannot create, edit, or run VBA macros, so open the workbook in the desktop app to use this code.

Assumptions and choices to check first

The macro treats row 1 of the first worksheet as headers and rows 2 onward as data. Every source file should use the same fields in the same order. The code detects a different number of used columns and stops with an error, but it cannot reliably detect two files that have the same number of columns with different fields rearranged. Confirm that headers and column order match before combining files.

Choose the right folder. The file search includes common Excel formats matching *.xls*, including .xls, .xlsx, .xlsm, and .xlsb. It also skips Excel’s temporary lock files, whose names begin with ~$.
Choose the right sheet. Only the first worksheet in each workbook is read. If the data is on another sheet, move it to the first position or adapt the code to select a sheet by name.
Choose the right output workbook. The macro uses ThisWorkbook, meaning the workbook that contains the code. Put the code in the destination workbook, not only in a separate workbook or the Personal Macro Workbook.
Check for an existing output sheet. If Consolidated already exists, the macro clears that entire sheet before importing. Rename or preserve any existing content you need before running it.

What happens to formulas, formatting, and empty rows

The assignment through Value2 transfers calculated values rather than formulas. That is useful when source formulas refer to other sheets or workbooks that should not become live dependencies in the combined dataset. The macro does not copy number formats, cell colors, comments, hyperlinks, charts, images, or worksheet-level settings. Apply a consistent table style or number format to the combined data afterward if presentation matters.

Rows between the header and the last nonempty row are included even if some cells or entire intermediate rows are blank. A completely empty first worksheet is skipped. A workbook containing only a header can establish the output headers but adds no data rows. The macro searches for content, including formulas, so cells with formulas that display an empty string can still affect the detected last row or column.

Common problems and safe troubleshooting

The macro is blocked. Only enable macros for files and sources you trust. If the workbook came from the internet, Windows or your organization’s security policy may block macros; follow your organization’s approved process rather than lowering security settings broadly.
The wrong data appears. Confirm that the desired data is on the first worksheet and that headers are in row 1. Check whether a title or notes occupy the top rows; this example does not search for a header row automatically.
The macro stops because of different column counts. Compare the files’ used columns and standardize their layouts, or build a mapping that matches fields by header name. Do not simply remove the check unless you have verified that unlike columns will not be misaligned.
The output is incomplete or takes a long time. Close unnecessary workbooks, check that the selected folder is correct, and consider splitting a very large folder into smaller groups. Excel worksheets have a finite row capacity, so a combined dataset cannot exceed the destination sheet’s available rows.
Links or calculated values seem unexpected. The macro opens sources with link updates disabled and reads current cell values. If a source workbook has not recalculated or saved its latest results, the imported values may not represent the figures you expected.

During an error, the macro attempts to close the currently open source workbook without saving it and restore Excel’s screen-updating and event settings. It may leave partial results on Consolidated if it stopped after importing earlier files. Review or clear those results before trying again; the macro does not provide a transaction-style rollback.

When to use a different approach

This VBA method is suitable when the files have a stable layout, the task is repeated, and a desktop Excel workflow is acceptable. It is less suitable when columns change names or positions often, when data must be merged from multiple sheets, or when users need a refreshable process with minimal code maintenance.

Power Query: For a repeatable folder import, use Data > Get Data > From File > From Folder in Excel for Microsoft 365 on Windows. Power Query can combine files with a common structure and refresh the query later. Inspect its sample-file and transformation steps, particularly when files have inconsistent headers or extra worksheets.
Copying whole worksheets: If the goal is to keep each file’s layout as a separate tab, copying worksheets into a new workbook is more appropriate than stacking rows. That produces one workbook, but it does not create one unified table.
Manual copy and paste: For a few small files, manual copying can be easier to inspect. Copy the header once, then append only data rows from later files, checking column order as you go.

Power Query and VBA solve related but different problems. Power Query is designed to import and transform data through a refreshable process; this macro runs code and writes a static snapshot of values. Choose based on whether you need repeatable refreshes, code-based automation, or simply a one-time merge.

Frequently Asked Questions

Can this macro combine all worksheets in each Excel file?
No. It reads only the first worksheet in each source workbook. Combining multiple sheets requires an additional loop through each workbook’s Worksheets collection and a decision about how to handle different headers and layouts.

Does the macro keep the original formulas?
No. It writes the values currently held by the source cells. This avoids carrying formulas and external references into the destination, but it also means the combined output will not update when a source workbook changes.

Will the code work with CSV files?
The file search is for Excel workbook extensions, not CSV files. CSV files can be opened by Excel, but supporting them reliably requires changing the file selection and import logic. For a mixed or CSV-heavy folder, Power Query’s folder import is often a better fit.

Can I run VBA in Excel for the web?
No. Excel for the web can preserve a workbook containing VBA, but it cannot run or edit the VBA macro. Use Excel desktop on Windows or Mac for this task.

Can I undo the macro with Ctrl+Z?
No. Running VBA clears and writes cells outside Excel’s normal undo history. Save a separate copy before running it, and inspect the output before replacing any source data.

How can I combine files when their columns are in a different order?
First standardize the files or use a process that maps fields by header name. This macro appends values by column position, so matching column counts alone do not guarantee that the resulting records are correctly aligned.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.