How to Combine Multiple Excel Files into One Worksheet Using Macro

In this article, I’ll show you how you can combine multiple excel files into one worksheet using a Macro.


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

Sub Combine_Multiple_Files_Horizontally()

Dim Source_Files() As Variant
Source_Files = Array("Hardware Accessories.xlsx", "Clothing.xlsx")

Destination_File = "Combined_File.xlsm"
Destination_Worksheet = "Horizontal"

Starting_Row = 2
Starting_Column = 2
Gap = 1

For i = LBound(Source_Files) To UBound(Source_Files)
    Workbooks.Open Source_Files(I)
    For j = 1 To Workbooks(Source_Files(i)).Sheets.Count
        Workbooks(Source_Files(i)).Worksheets(j).Activate
        ActiveSheet.UsedRange.Copy
        Range_Width = ActiveSheet.UsedRange.Columns.Count
        Workbooks(Destination_File).Worksheets(Destination_Worksheet).Activate
        ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll
        Starting_Column = Starting_Column + Range_Width + Gap   
    Next j
    Workbooks(Source_Files(i)).Close
Next i

Application.CutCopyMode = False

End Sub

VBA Code to Combine Multiple Files into One Worksheet Using Macro


Download Practice Workbook

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


How to Combine Multiple Excel Files into One Worksheet Using Macro

Here I have got two Excel files, one is called “Hardware Accessories.xlsx” which contains 3 worksheets called 2019, 2020, and 2021.

Each of them contains the sales record of some hardware items of the month of January and February of the respective year.

Excel File to Combine Multiple Excel Files into One Worksheet Using a Macro

The other file is called “Clothing.xlsx”. It also contains 3 worksheets called 2019, 2020, and 2021 each of which contains the sales record of some clothing items of the month January and February of the respective year.

So, without further delay, let’s continue to our main discussion. We’ll develop a Macro to combine worksheets from the multiple excel files into one worksheet today.

We’ll accomplish this in 4 ways possible.


1. Macro to Combine Multiple Excel Files into One Worksheet Horizontally

First of all, we’ll develop a Macro to combine the worksheets from these multiple files horizontally.

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

Here I’ve opened a new workbook called “Combined_File.xlsm”. There I’ve created a worksheet called “Horizontal”.

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 file inputs into the code. These are the source files names that we’ll combine, the name of the destination workbook, and the destination worksheet.

Dim Source_Files() As Variant
Source_Files = Array("Hardware Accessories.xlsx", "Clothing.xlsx")

Destination_File = "Combined File.xlsm"
Destination_Worksheet = "Horizontal"

⧪ Step 2: Entering the Starting Row, Starting Column, and the Gap between the Data Sets

Next, we’ve to enter the starting row, starting column, and the gap between the data sets. Here the starting row is 2, the starting column is also 2 (B2), and the gap is 1.

Starting_Row = 2
Starting_Column = 2
Gap = 1

⧪ Step 3: Iterating through Two For-Loops to Combine All the Files into One Worksheet

This is the most important step. We’ll iterate through two for-loops to combine all the files into one worksheet.

For i = LBound(Source_Files) To UBound(Source_Files)
    Workbooks.Open Source_Files(i)
    For j = 1 To Workbooks(Source_Files(i)).Sheets.Count
        Workbooks(Source_Files(i)).Worksheets(j).Activate
        ActiveSheet.UsedRange.Copy
        Range_Width = ActiveSheet.UsedRange.Columns.Count
        Workbooks(Destination_File).Worksheets(Destination_Worksheet).Activate
        ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll
        Starting_Column = Starting_Column + Range_Width + Gap
    Next j
    Workbooks(Source_Files(i)).Close
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_Multiple_Files_Horizontally()

Dim Source_Files() As Variant
Source_Files = Array("Hardware Accessories.xlsx", "Clothing.xlsx")

Destination_File = "Combined_File.xlsm"
Destination_Worksheet = "Horizontal"

Starting_Row = 2
Starting_Column = 2
Gap = 1

For i = LBound(Source_Files) To UBound(Source_Files)
    Workbooks.Open Source_Files(i)
    For j = 1 To Workbooks(Source_Files(i)).Sheets.Count
        Workbooks(Source_Files(i)).Worksheets(j).Activate
        ActiveSheet.UsedRange.Copy
        Range_Width = ActiveSheet.UsedRange.Columns.Count
        Workbooks(Destination_File).Worksheets(Destination_Worksheet).Activate
        ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll
        Starting_Column = Starting_Column + Range_Width + Gap
    Next j
    Workbooks(Source_Files(i)).Close
Next i

Application.CutCopyMode = False

End Sub

VBA Code to Combine Multiple Files into One Worksheet Using Macro

⧭ Output:

Run the code. It’ll combine all the worksheets from all the source files vertically starting from cell B2 with a column gap of 1.

Output to Combine Multiple Excel Files into One Worksheet Using a Macro

⧭ Note:

Don’t forget to keep the source files and the destination file in the same folder on your computer. Otherwise, the code won’t work. This is applicable to all the methods described here.

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


2. Macro to Combine Multiple Excel Files 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 files and the destination file and worksheet.

Then enter the starting row, starting column, and the gap between the datasets.

And finally, combine the files with two for-loops.

The complete VBA code will be:

⧭ VBA Code:

Sub Combine_Multiple_Files_Vertically()

Dim Source_Files() As Variant
Source_Files = Array("Hardware Accessories.xlsx", "Clothing.xlsx")

Destination_File = "Combined_File.xlsm"
Destination_Worksheet = "Vertical"

Starting_Row = 2
Starting_Column = 2
Gap = 1

For i = LBound(Source_Files) To UBound(Source_Files)
    Workbooks.Open Source_Files(i)
    For j = 1 To Workbooks(Source_Files(i)).Sheets.Count
        Workbooks(Source_Files(i)).Worksheets(j).Activate
        ActiveSheet.UsedRange.Copy
        Range_Height = ActiveSheet.UsedRange.Rows.Count
        Workbooks(Destination_File).Worksheets(Destination_Worksheet).Activate
        ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll
        Starting_Row = Starting_Row + Range_Height + Gap
    Next j
    Workbooks(Source_Files(i)).Close
Next i

Application.CutCopyMode=False

End Sub

VBA Code to Combine Multiple Excel Files into One Worksheet Using a Macro

⧭ Output:

Run the code. It’ll combine all the worksheets from all the source files horizontally starting from cell B2 with a row gap of 1.

Related Content: Combine Multiple Excel Files into One Workbook with Separate Sheets


Similar Readings


3. Macro to Combine the Worksheets of the Same Excel File Horizontally, Then All the Files Vertically

We’ve learned to combine worksheets from the source workbooks both horizontally and vertically into one worksheet.

Now we’ll develop a Macro to combine the sheets from the same file horizontally, then all the files vertically.

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

First insert the source files and the destination file and worksheet.

Then enter the starting row, starting column, and the gap between the datasets.

And finally, combine the files with two for-loops.

The complete VBA code will be:

⧭ VBA Code:

Sub Combine_Multiple_Files_Horizontally_and_Vertically()

Dim Source_Files() As Variant
Source_Files = Array("Hardware Accessories.xlsx", "Clothing.xlsx")

Destination_File = "Combined_File.xlsm"
Destination_Worksheet = "First Horizontal, then Vertical"

Starting_Row = 2
Starting_Column = 2
Row_Gap = 1
Column_Gap = 1

Original_Starting_Column = Starting_Column

For i = LBound(Source_Files) To UBound(Source_Files)
    Workbooks.Open Source_Files(i)
    For j = 1 To Workbooks(Source_Files(i)).Sheets.Count
        Workbooks(Source_Files(i)).Worksheets(j).Activate
        ActiveSheet.UsedRange.Copy
        Range_Width = ActiveSheet.UsedRange.Columns.Count
        Workbooks(Destination_File).Worksheets(Destination_Worksheet).Activate
        ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll
        Starting_Column = Starting_Column + Range_Width + Column_Gap
    Next j
    Range_Height = ActiveSheet.UsedRange.Rows.Count
    Starting_Row = Starting_Row + Range_Height + Row_Gap
    Starting_Column = Original_Starting_Column
    Workbooks(Source_Files(i)).Close
Next i

Application.CutCopyMode = False

End Sub

VBA Code to Combine Multiple Excel Files into One Worksheet Using a Macro

⧭ Output:

Run the code. It’ll combine all the worksheets of the same file horizontally, then all the files vertically with a row gap of 1 and column gap of also 1.

Read More: How to Merge Multiple Sheets into One Sheet with VBA in Excel (2 Ways)


4. Macro to Combine the Worksheets of the Same Excel File Vertically, Then All the Files Horizontally

Now we’ll develop a Macro to combine the sheets from the same file vertically, then all the files horizontally.

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

First insert the source files and the destination file and worksheet.

Then enter the starting row, starting column, and the gap between the datasets.

And finally, combine the files with two for-loops.

The complete VBA code will be:

⧭ VBA Code:

Sub Combine_Multiple_Files_Vertically_and_Horizontally()

Dim Source_Files() As Variant
Source_Files = Array("Hardware Accessories.xlsx", "Clothing.xlsx")

Destination_File = "Combined_File.xlsm"
Destination_Worksheet = "First Vertical, then Horizontal"

Starting_Row = 2
Starting_Column = 2
Row_Gap = 1
Column_Gap = 1

Original_Starting_Row = Starting_Row

For i = LBound(Source_Files) To UBound(Source_Files)
    Workbooks.Open Source_Files(i)
    For j = 1 To Workbooks(Source_Files(i)).Sheets.Count
        Workbooks(Source_Files(i)).Worksheets(j).Activate
        ActiveSheet.UsedRange.Copy
        Range_Height = ActiveSheet.UsedRange.Rows.Count
        Workbooks(Destination_File).Worksheets(Destination_Worksheet).Activate
        ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll
        Starting_Row = Starting_Row + Range_Height + Row_Gap
    Next j
    Range_Width = ActiveSheet.UsedRange.Columns.Count
    Starting_Column = Starting_Column + Range_Width + Column_Gap
    Starting_Row = Original_Starting_Row
    Workbooks(Source_Files(i)).Close
Next i

Application.CutCopyMode = False

End Sub

VBA Code to Combine Multiple Excel Files into One Worksheet Using a Macro

⧭ Output:

Run the code. It’ll combine all the worksheets of the same file vertically, then all the files horizontally with a row gap of 1 and column gap of also 1.

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


Things to Remember

You must keep the source files and the destination file in the same folder of your computer for all the codes mentioned in this article to work properly. Otherwise, they won’t work.

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

Click here to know them in detail.


Conclusion

So these are all possible ways to combine multiple Excel files 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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo