3 Simple Ways to Combine Sheets in Excel with VBA

Combining sheets in Microsoft Excel can transform your data management from a tedious task into a streamlined process. While Excel provides several methods to merge sheets manually, using VBA (Visual Basic for Applications) automates this process, saving time and reducing errors. Here are three effective ways to combine sheets using VBA, tailored for different scenarios you might encounter.

Method 1: Using VBA to Merge Multiple Sheets into One

This method is ideal when you have several sheets within the same workbook, each containing data you need to merge into a single, cohesive dataset.

Steps to Merge Sheets

1. Open Excel and press ALT + F11 to open the VBA editor.
2. In the VBA editor, go to Insert > Module to create a new module.
3. Copy and paste the following code into the new module:

Sub MergeMultipleSheets()
    Dim ws As Worksheet
    Dim lastrow As Long
    Dim mainSheet As Worksheet
    Dim cellVal As Variant
    Set mainSheet = ThisWorkbook.Worksheets(“Sheet1”)

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> mainSheet.Name Then
        lastrow = mainSheet.Cells(mainSheet.Rows.Count, "A").End(xlUp).Row + 1
        mainSheet.Cells(lastrow, "A").Value = ws.Name
        ws.UsedRange.Offset(1).Copy mainSheet.Cells(lastrow + 1, "A")
    End If
Next ws


End Sub

4. Replace “Sheet1” with the name of the sheet where you want all the data to be merged.
5. Run the macro by pressing F5 or by selecting Run in the VBA editor.

🚀 Note: Ensure your destination sheet ("Sheet1" in the example) is either empty or contains headers in the first row before running the macro. The script will overwrite data below the first row!

Method 2: Combining Sheets from Different Workbooks

This approach is useful if you're working with data spread across multiple Excel files but wish to consolidate it into one workbook.

Steps for Inter-Workbook Data Integration

1. With Excel open, go to the VBA editor with ALT + F11.
2. Create a new module by going to Insert > Module.
3. Input the following code:

Sub MergeFromDifferentWorkbooks()
    Dim ws As Worksheet, wbSource As Workbook, mainWB As Workbook
    Dim fd As FileDialog, filesPath As Variant, fileName As String
    Dim lastRow As Long, currRow As Long

Set mainWB = ThisWorkbook
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
    .Title = "Select Workbooks to Merge"
    .AllowMultiSelect = True
    If .Show = -1 Then
        For Each filesPath In .SelectedItems
            Set wbSource = Workbooks.Open(filesPath)
            For Each ws In wbSource.Worksheets
                lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
                currRow = mainWB.Sheets(1).Cells(mainWB.Sheets(1).Rows.Count, "A").End(xlUp).Row + 1
                ws.UsedRange.Copy Destination:=mainWB.Sheets(1).Cells(currRow, 1)
            Next ws
            wbSource.Close SaveChanges:=False
        Next filesPath
    End If
End With


End Sub

4. Save the file as .xlsm to enable macro functions.
5. Run the macro to open the file dialog and select your source workbooks.

🚨 Note: This script will merge all sheets from the selected workbooks into the active sheet of your workbook.

Method 3: Merging Data Based on a Common Key

When your data needs to be combined based on specific criteria like IDs or names, this method provides a structured approach using VBA.

Steps for Key-Based Data Merging

1. Open the VBA editor (ALT + F11).
2. Create a new module.
3. Insert the following code:

Sub MergeByColumnKey()
    Dim mainWS As Worksheet, secWS As Worksheet
    Dim mainCell As Range, secCell As Range
    Dim mainKey As String, secKey As String
    Dim lastRowMain As Long, lastRowSec As Long, foundMatch As Boolean

Set mainWS = ThisWorkbook.Sheets("Sheet1")
Set secWS = ThisWorkbook.Sheets("Sheet2")

lastRowMain = mainWS.Cells(mainWS.Rows.Count, "A").End(xlUp).Row
lastRowSec = secWS.Cells(secWS.Rows.Count, "A").End(xlUp).Row

For Each mainCell In mainWS.Range("A2:A" & lastRowMain)
    mainKey = mainCell.Value
    foundMatch = False
    For Each secCell In secWS.Range("A2:A" & lastRowSec)
        secKey = secCell.Value
        If mainKey = secKey Then
            mainWS.Cells(mainCell.Row, "B").Resize(1, secWS.UsedRange.Columns.Count).Value = secWS.Cells(secCell.Row, 2).Resize(1, secWS.UsedRange.Columns.Count).Value
            foundMatch = True
            Exit For
        End If
    Next secCell
    If Not foundMatch Then mainWS.Cells(mainCell.Row, "B").Value = "No Match"
Next mainCell


End Sub

4. Execute the macro to merge the sheets.

Each of these methods offers a unique way to deal with data consolidation in Excel using VBA. Whether you're merging all data from multiple sheets or looking to align specific datasets based on a key, these solutions can enhance your productivity and data analysis capabilities.

The automation provided by VBA not only speeds up the merging process but also reduces the likelihood of human error. By mastering these techniques, you can tackle large datasets with ease, allowing for more focus on data analysis rather than data preparation.

What are the benefits of using VBA to merge sheets?

+

VBA offers automation, which saves time, reduces errors, and allows for handling large amounts of data efficiently. It also provides custom solutions tailored to specific data merging needs.

Can VBA handle merging data from sheets with different structures?

+

Yes, but it requires more complex scripting. VBA can adapt to different data structures with conditional logic and flexible data mapping.

What if my sheets have headers I don’t want to merge?

+

You can modify the VBA code to start copying data from the second row onwards, skipping headers. Ensure the destination sheet has the correct headers before merging.

Is it possible to undo a merge operation in Excel?

+

Once data is merged and saved, Excel does not have an “undo merge” function. Always backup your files before running macros.