How to Combine Multiple Excel Sheets into One Using Macro (3 Methods)

In this article, I’ll show you how you can combine multiple excel sheets into one Macro with Visual Basic of Applications (VBA).

How to Combine Multiple Excel Files into One Worksheet Using Macro (Quick View)

Sub Combine_Sheets_Horizontally()

Dim Sheet_Names() As Variant
Sheet_Names = Array("January", "February", "March")

Destination_Sheet = "Combined Sheet (Horizontally)"

Gap = 1

Set Destination_Cell = Worksheets(Sheet_Names(0)).UsedRange.Cells(1, 1)
Starting_Row = Destination_Cell.Row
Starting_Column = Destination_Cell.Column

For i = LBound(Sheet_Names) To UBound(Sheet_Names)
    Worksheets(Sheet_Names(i)).Activate
    Row_Width = ActiveSheet.UsedRange.Columns.Count
    ActiveSheet.UsedRange.Copy
    Worksheets(Destination_Sheet).Activate
    ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll
    Starting_Column = Starting_Column + Row_Width + Gap
Next i

Application.CutCopyMode = False

End Sub

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

How to Combine Multiple Excel Sheets into One Macro with VBA

Here I’ve got an Excel workbook that contains three worksheets titled January, February. March respectively.

Each contains the sales record of some books of three different months of a bookstore.

So, without further delay, let’s continue to our main discussion. We’ll develop a Macro to combine these multiple sheets into one worksheet.

We’ll accomplish this in 3 ways possible.

1. Macro to Combine Multiple Excel Sheets Into One Worksheet Horizontally

First of all, we’ll develop a Macro to combine the worksheets into one worksheet horizontally.

Open the new workbook and the worksheet where you want to merge these files.

Here I’ve opened a new worksheet called “Combined Sheet (Horizontally)”.

Then we have to enter the VBA code to develop the Macro. We’ll break the code step-by-step for better understanding and learning.

⧪ Step 1: Entering the Source Files and the Destination File

First of all, we’ve to enter the inputs into the code. These are the names of the sheets that we’ll combine (January, February, and March in this example), the name of the destination worksheet( Combined Sheet (Horizontally) here), and the gap between the data sets from different worksheets into the combined sheet (1 here, set it according to your need).

Dim Sheet_Names() As Variant
Sheet_Names = Array("January", "February", "March")

Destination_Sheet = "Combined Sheet (Horizontally)"

Gap = 1

⧪ Step 2: Setting the Destination Cell, Starting Row, and Starting Column

Next, we’ll set the destination cell, starting row, and starting column of the combined data. The starting will be the same as the starting cell of the first worksheet.

Set Destination_Cell = Worksheets(Sheet_Names(0)).UsedRange.Cells(1, 1)
Starting_Row = Destination_Cell.Row
Starting_Column = Destination_Cell.Column

⧪ Step 3: Iterating through a For-Loop to Combine All the Sheets into One Worksheet

This is the most important step. We’ll iterate through a for-loop to combine all the sheets into one worksheet.

For i = LBound(Sheet_Names) To UBound(Sheet_Names)
    Worksheets(Sheet_Names(i)).Activate
    Row_Width = ActiveSheet.UsedRange.Columns.Count
    ActiveSheet.UsedRange.Copy
    Worksheets(Destination_Sheet).Activate
    ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll
    Starting_Column = Starting_Column + Row_Width + Gap
Next i

⧪ Step 4 (Optional): Turning Off the CutCopyMode

Finally, we’ll turn off the CutCopyMode of VBA. You can skip this step if you want.

Application.CutCopyMode = False

Therefore, the complete VBA code will be:

⧭ VBA Code:

Sub Combine_Sheets_with_Operation()

Dim Sheet_Names() As Variant
Sheet_Names = Array("January", "February", "March")

Destination_Sheet = "Combined Sheet (Horizontally)"

Gap = 1

Set Destination_Cell = Worksheets(Sheet_Names(0)).UsedRange.Cells(1, 1)
Starting_Row = Destination_Cell.Row
Starting_Column = Destination_Cell.Column

For i = LBound(Sheet_Names) To UBound(Sheet_Names)
    Worksheets(Sheet_Names(i)).Activate
    Row_Width = ActiveSheet.UsedRange.Columns.Count
    ActiveSheet.UsedRange.Copy
    Worksheets(Destination_Sheet).Activate
    ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll
    Starting_Column = Starting_Column + Row_Width + Gap
Next i

Application.CutCopyMode = False

End Sub

⧭ Output:

Run the code. It’ll combine all the input worksheets horizontally into the destination worksheet (Combined Sheet (Horizontally) here).

Read More: How to Merge Multiple Excel Files into One Sheet (4 Methods)

2. Macro to Combine Multiple Excel Sheets Into One Worksheet Vertically

Now we’ll develop a Macro to combine multiple excel files into one worksheet vertically.

We’ll follow the same steps as mentioned in method 1.

First, insert the source worksheets (January, February, and March), the destination worksheet (a new worksheet called Combined Sheet (Vertically) here), and the gap (Again 1 here).

Then set the destination cell, starting row, and starting column.

And finally, combine the sheets with a for-loop.

The complete VBA code will be:

⧭ VBA Code:

Sub Combine_Sheets_Vertically()

Dim Sheet_Names() As Variant
Sheet_Names = Array("January", "February", "March")

Destination_Sheet = "Combined Sheet (Vertically)"

Gap = 1
Set Destination_Cell = Worksheets(Sheet_Names(0)).UsedRange.Cells(1, 1)
Starting_Row = Destination_Cell.Row
Starting_Column = Destination_Cell.Column

For i = LBound(Sheet_Names) To UBound(Sheet_Names)
    Worksheets(Sheet_Names(i)).Activate
    Row_Height = ActiveSheet.UsedRange.Rows.Count
    ActiveSheet.UsedRange.Copy
    Worksheets(Destination_Sheet).Activate
    ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll
    Starting_Row = Starting_Row + Row_Height + Gap
Next i

Application.CutCopyMode = False

End Sub

⧭ Output:

Run the code. It’ll combine all the worksheets horizontally in the destination worksheet (Combined Sheet (Horizontally) here).

Read More: How to Combine Data from Multiple Sheets in Excel (4 Ways)

Similar Readings

• How to Consolidate Data in Excel from Multiple Worksheets (3 Ways)
• How to Combine Two Scatter Plots in Excel (Step by Step Analysis)
• Excel VBA: Combine Date and Time (3 Methods)
• How to Combine Name and Date in Excel (7 Methods)
• How to Combine Graphs in Excel (Step-by-Step Guideline)

3. Macro to Combine Multiple Excel Sheets Into One Worksheet with an Operation

We’ve learned to combine worksheets both horizontally and vertically into one worksheet.

Now we’ll develop a Macro to combine the sheets into one worksheet with a specific operation (Addition, Subtraction, Multiplication, or Division)

Again, we’ll follow the same steps as mentioned in methods 1 and 2.

First, insert the source worksheets (January, February, and March), the destination worksheet (a new worksheet called Combined Sheet (with Operation) here), the columns on which the operation will be implied (2 and 3 here), and the operation (Addition here).

Then set up the destination cell, starting row, and starting column.

And finally combining the files with a for-loop.

The complete VBA code will be:

⧭ VBA Code:

Sub Combine_Sheets_with_Operation()

Dim Sheet_Names() As Variant
Sheet_Names = Array("January", "February", "March")

Destination_Sheet = "Combined Sheet (with Operation)"

Dim Operation_Columns() As Variant
Operation_Columns = Array(2, 3)

Operation = "Addition"

Set Destination_Cell = Worksheets(Sheet_Names(0)).UsedRange.Cells(1, 1)
Starting_Row = Destination_Cell.Row
Starting_Column = Destination_Cell.Column

Worksheets(Sheet_Names(0)).Activate

ActiveSheet.UsedRange.Copy

Worksheets(Destination_Sheet).Activate

ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll

For i = LBound(Sheet_Names) + 1 To UBound(Sheet_Names)
    Worksheets(Sheet_Names(i)).Activate
    For j = LBound(Operation_Columns) To UBound(Operation_Columns)
        ActiveSheet.UsedRange.Range(Cells(2, Operation_Columns(j)), Cells(ActiveSheet.UsedRange.Rows.Count, Operation_Columns(j))).Copy
        Worksheets(Destination_Sheet).Activate
        If Operation = "Addition" Then
            ActiveSheet.UsedRange.Cells(2, Operation_Columns(j)).PasteSpecial Operation:=xlAdd
        ElseIf Operation = "Subtraction" Then
            ActiveSheet.UsedRange.Cells(2, Operation_Columns(j)).PasteSpecial Operation:=xlSubtract
        ElseIf Operation = "Multiplication" Then
            ActiveSheet.UsedRange.Cells(2, Operation_Columns(j)).PasteSpecial Operation:=xlMultiply
        ElseIf Operation = "Division" Then
            ActiveSheet.UsedRange.Cells(2, Operation_Columns(j)).PasteSpecial Operation:=xlDivide
        Else
           MsgBox "Enter either Addition, Subtraction, Multiplication, or Division as Operation."
        End If
    Next j
Next i

Application.CutCopyMode = False

End Sub

⧭ Output:

Run the code. It’ll combine the input worksheets with adding data of columns 2, and 3, into the worksheet called Combined Sheet (with Operation).

Read More: Combine Multiple Excel Files into One Workbook with Separate Sheets

Things to Remember

Here, we’ve used the xlPasteAll property of the PasteSpecial method of VBA to copy everything from the source sheets to the destination sheets. Other than this, there are 11 more properties of the PasteSpecial method of VBA.

Click here to know them in detail.

While combining worksheets with large data sets, the codes may take a bit of time to run. So have patience and wait until they run successfully.

Conclusion

So these are all possible ways to combine multiple Excel sheets into one worksheet using a Macro with VBA. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more pasts and updates.

Related Articles

• How to Merge Multiple Sheets into One Sheet with VBA in Excel (2 Ways)
• Combine Graphs in Excel (Step-by-Step Guideline)
• How to Combine Two Graphs in Excel (2 Methods)
• Combine Two Line Graphs in Excel (3 Methods)
• How to Combine Graphs with Different X Axis in Excel
• How to Combine Two Bar Graphs in Excel (5 Ways)