Blog

In this comprehensive guide, you’ll learn how to automatically merge data from multiple Excel sheets into a single master sheet using VBA.

• Introduction
• Sample Sales Reports and the Goal of Merging
• Steps to Create a Macro in VBA
• Step-by-Step Code Analysis
• Complete Code for the Merge Sheets Macro
• Video of the Macro in Action
• Introducing the Advanced Sheet Merging Tool
• Invitation to Engage in the Comments

Introduction

In many businesses, sales reports for different units are prepared separately but share the same structure. For example, your company might record weekly sales for various departments in separate worksheets. Manually compiling this data is time-consuming and prone to errors. Using VBA in Excel allows you to automatically merge multiple sheets with an identical structure, increasing your speed and accuracy.

Merging Sales Reports Using VBA

For example, let’s assume we have two sales units:

Unit A

Unit B

The Goal of Merging

To create a single master sheet that includes all sales data from both units without manual copy-pasting. This reduces errors, saves time, and facilitates data analysis.

Steps to Create a Macro in VBA

1. Go to the Developer tab and select Visual Basic. You can find more information on how to enable the Developer tab on the How to enable the Developer tab in Excel page.
2. From the Insert menu, choose Module to create a new module. You can see a video tutorial on how to add a standard module in VBA on the Modules in VBA page.

Step-by-Step Breakdown of the Merge Sheets Code

Next, we’ll review the code piece by piece to understand how each part works.

1. Declaring Necessary Variables

Dim ws As Worksheet ' Variable for each sheet
Dim wsMaster As Worksheet ' The destination sheet for merged data
Dim rng As Range ' The range to be copied from each sheet
Dim lastRow As Long, lastCol As Long
Dim pasteRow As Long

In this section, all the variables required to run the macro are defined.

2. Error Handling and Preparing the Destination Sheet

On Error Resume Next
Set wsMaster = ThisWorkbook.Sheets("MergedData")
If wsMaster Is Nothing Then
Set wsMaster = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
wsMaster.Name = "MergedData"
Else
wsMaster.Cells.Clear
wsMaster.Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
On Error GoTo 0

• On Error Resume Next ensures that the code does not stop if an error occurs.
• If the destination sheet doesn’t exist, it is created. If it already exists, its content is cleared, and it’s moved to the last position among the sheets. In this code, we use the wsMaster variable to refer to the destination sheet. Using the Set statement and the ThisWorkbook object, we assign the sheet named "MergedData" from the Sheets collection as the reference for this variable. We then check if wsMaster is Nothing. If it is, the sheet does not exist and we must create it. Otherwise, the sheet already exists, and its contents must be completely cleared.
• You can find more information about Excel objects on the Excel Objects in VBA page.

3. Customizing Sheet Appearance

wsMaster.Tab.Color = RGB(255, 0, 0)
pasteRow = 1

Here, the tab color of the destination sheet is changed to red, and the pasteRow variable is initialized to start copying data from the first row.

4. The Data Merging Loop

For Each ws In ThisWorkbook.Sheets
If ws.Name <> wsMaster.Name Then
lastRow = ws.Cells.Find("", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastCol = ws.Cells.Find("", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)) rng.Copy wsMaster.Cells(pasteRow, 1) pasteRow = wsMaster.Cells(wsMaster.Rows.Count, 1).End(xlUp).Row + 1
End If Next ws

In this section, for every sheet except the destination sheet:

• The last row and column containing data are found.
• The data range is selected and copied to the destination sheet.
• The pasteRow variable is updated to set the starting point for the next sheet’s data.

5. Displaying the Final Message

MsgBox "All sheets were successfully merged into 'MergedData'!", vbInformation

After the process is complete, a success message is displayed.

Complete Macro for Merging Excel Sheets

The complete code for merging the sheets is as follows:

Sub MergeSheets()
Dim ws As Worksheet
Dim wsMaster As Worksheet
Dim rng As Range
Dim lastRow As Long, lastCol As Long
Dim pasteRow As Long ' Create or clear the destination sheet
On Error Resume Next
Set wsMaster = ThisWorkbook.Sheets("MergedData")
If wsMaster Is Nothing Then Set wsMaster = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) wsMaster.Name = "MergedData"
Else wsMaster.Cells.Clear wsMaster.Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
On Error GoTo 0 ' Set the tab color to red
wsMaster.Tab.Color = RGB(255, 0, 0) pasteRow = 1 ' Loop through all worksheets in the workbook
For Each ws In ThisWorkbook.Sheets If ws.Name <> wsMaster.Name Then ' Find the last used row and column lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row lastCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column ' Set the data range Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)) ' Copy the range to the destination sheet rng.Copy wsMaster.Cells(pasteRow, 1) ' Update the row for the next paste pasteRow = wsMaster.Cells(wsMaster.Rows.Count, 1).End(xlUp).Row + 1 End If
Next ws MsgBox "All sheets were successfully merged into 'MergedData'!", vbInformation End Sub

Video of the Macro in Action

The video below shows the macro running and the final result in the MergedData sheet:

Introducing the Advanced Sheet Merging Tool

For more professional merging tasks, we have developed an advanced macro with the following features:

• Merge reports with the same structure from multiple Excel files.
• Add a source column to identify the origin of the data.
• Automatically calculate subtotals.
• Support for various date formats.
• Report potential errors.

This tool is highly practical for sales managers and data analysts, saving up to 90% of your time.

Purchase Advanced Version

Invitation to Engage in the Comments

Feel free to share your experiences in the comments section and ask your questions about VBA and merging sheets. We are here to help!

If you found this post helpful, please share it with your friends.