How to Combine All Excel Sheets Automatically

Combining dozens or even hundreds of Excel sheets into one is a tedious task if done manually. Fortunately, by leveraging the power of VBA Macro, this complex process can be automated with just one click, saving you significant time and effort.

For professionals who frequently handle data consolidation from various departments or periodic reports, this technique is essential. Using a VBA Macro to combine Excel sheets is not only efficient but also minimizes the risk of human error common in manual data copying.

Advantages of Using VBA Macro

Compared to conventional copy-paste methods, automation with VBA offers several benefits. First, the execution process is very fast. Second, data consistency is guaranteed as it is managed by the script. In addition, you can customize the code to fit specific needs, such as combining data without repeating headers.

VBA Script to Combine All Sheets

Below is a reliable and tested VBA script. This code will merge data from all sheets in a workbook, except the resulting combined sheet itself, into a new sheet named “RDBMergeSheet.

Sub CopyDataWithoutHeaders()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim shLast As Long
    Dim CopyRng As Range
    Dim StartRow As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Delete the sheet "RDBMergeSheet" if it exists
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Create a new worksheet named "RDBMergeSheet"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "RDBMergeSheet"
    StartRow = 2

    'Loop through all worksheets and copy data to DestSh
    For Each sh In ActiveWorkbook.Worksheets
        If IsError(Application.Match(sh.Name, _
                                     Array(DestSh.Name, "Information"), 0)) Then
            Last = LastRow(DestSh)
            shLast = LastRow(sh)

            If shLast > 0 And shLast >= StartRow Then
                Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

                If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                    MsgBox "Not enough space in the destination sheet"
                    GoTo ExitTheSub
                End If

                CopyRng.Copy
                With DestSh.Cells(Last + 1, "A")
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                End With
            End If
        End If
    Next

ExitTheSub:
    Application.GoTo DestSh.Cells(1)
    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

'Support function to find the last row
Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious).Row
    On Error GoTo 0
End Function

Note that the script above requires the supporting function LastRow, which has been included to accurately detect the last row containing data.

How to Use This Script

1. Open your Excel workbook.
2. Press ALT + F11 to open the VBA Editor.
3. In the Project Explorer, right-click on your workbook, select Insert > Module.
4. Copy and paste the entire code above into the empty module window.
5. Close the VBA Editor and return to Excel.
6. Run the macro by pressing ALT + F8, select CopyDataWithoutHeaders, then click Run.

As a result, all data from various sheets will be neatly combined into a new sheet. Therefore, you can focus on data analysis rather than the collection process.

Important Tips and Considerations

Before running the macro, always save a copy of your workbook. Consistent data structure in each sheet (number of columns and data types) will ensure a perfect merge. However, if the structure differs, you may need to modify the code.

This VBA script to combine Excel sheets is a powerful foundation. You can further develop it, for example by adding filters or only merging specific sheets. To learn more about VBA, refer to trusted sources like Ron de Bruin’s Website.