Merge CSV Files into Multiple Sheets in Excel (with Easy Steps)

When working with Excel files, sometimes we come up with multiple CSV files. Now, it becomes a need for us frequently to merge those CSV files into a single workbook. In this article, I will show you all the steps to merge CSV files into multiple sheets in Excel.

Download Practice Workbook

You can download our source CSV files and final merged workbook from here for free!

CSV Source Files:

Final Merged File:

Steps to Merge CSV Files into Multiple Excel Sheets

Say, you have three individual Excel workbooks for grade 6, grade 7, and grade 8 report cards for various subjects for multiple students. The files are in CSV format with the students’ names, subjects, and scores. Now, you want to merge all the workbooks in a single workbook in multiple sheets. Go through the step-by-step guidelines to merge CSV files into multiple Excel sheets.

📌 Step 1: Prepare a Module for VBA

First and foremost, you need to insert and prepare a module for the required VBA code application.

• To do this, go to the Developer tab >> Visual Basic tool.

• As a result, the VB Editor window will open.
• Afterward, go to the Insert tab here and choose the Module option.

As a result, you will have a module now to insert your VBA code.

Read More: Excel VBA to Read CSV File into Array (4 Ideal Examples)

📌 Step 2: Write and Save the VBA Code

Following this, you will need to insert your VBA code now.

• In order to do this, first click on Module 1.
• Afterward, write the following code in the VB Editor of Module 1.

Sub MergeCSVFiles()
    Dim openFiles As Variant
    Dim I As Integer
    Dim wb As Workbook
    Dim mergedWb As Workbook
    Dim delimiter As String
    Dim screen As Boolean
    On Error GoTo ErrHandler
    screen = Application.ScreenUpdating
    Application.ScreenUpdating = False
    delimiter = ";"
    openFiles = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , "Merge CSV Files", , True)
    If TypeName(openFiles) = "Boolean" Then
        MsgBox "No files were selected", , "Merge CSV Files"
        GoTo ExitHandler
    End If
    I = 1
    Set mergedWb = Workbooks.Open(openFiles(I))
    mergedWb.Sheets(1).Copy
    Set wb = Application.ActiveWorkbook
    mergedWb.Close False
    Do While I < UBound(openFiles)
        I = I + 1
        Set mergedWb = Workbooks.Open(openFiles(I))
        mergedWb.Sheets(1).Move , wb.Sheets(wb.Sheets.Count)
    Loop
ExitHandler:
    Application.ScreenUpdating = screen
    Set wb = Nothing
    Set mergedWb = Nothing
    Exit Sub
ErrHandler:
    MsgBox Err.Description, , "Merge CSV Files"
    Resume ExitHandler
End Sub

• Subsequently, press Ctrl + S on your keyboard to save the VBA code.

• As a result, a Microsoft Excel dialogue box will appear.
• Subsequently, click on the No button.

• Consequently, the Save As window will appear.
• Following, change the Save as type: option as .xlsm format and click on the Save button.

Thus, your VBA code is written and saved perfectly.

Read More: Excel VBA to Read CSV File Line by Line (3 Ideal Examples)

Similar Readings

• How to Import Text File to Excel Using VBA (3 Easy Ways)
• How to Open Notepad or Text File in Excel with Columns (3 Easy Methods)
• Excel VBA: Read Text File into String (4 Effective Cases)
• How to Convert CSV to XLSX (4 Quick Methods)
• How to Read CSV File in Excel (4 Fastest Ways)

📌 Step 3: Run the Code to Merge CSV Files into Multiple Sheets

Last but not least, you need to run the VBA code now to merge CSV files into multiple sheets.

• To do this, go to the Developer tab >> Macros tool.

• As a result, the Macro window will appear.
• Subsequently, choose the MergeCSVFiles macro from the Macro name: pane and click on the Run button.

• Thus, all the workbooks will be merged into a new workbook as different worksheets.

• Now, to store the Excel file properly, go to the File tab.

• Following, click on the Save As option from the expanded File tab.

• As a result, the Excel Save As dialogue box will appear.
• Subsequently, click on the Browse option.

• Consequently, the Save As dialogue box will appear.
• Following, insert your desired file name in the File name: text box and click on the Save button.

Thus, the CSV files are merged into multiple sheets of a single workbook. And, the final outcome would look like this.

Read More: How to Import CSV into Existing Sheet in Excel (5 Methods)

Use of Power Query to Merge CSV Files into a Single Sheet in Excel

Now, if you want to merge CSV files into a single sheet in Excel, you can use the Excel power query tool. Follow the steps below to do this.

📌 Steps:

• First and foremost, go to the Data tab >> Get Data tool >> From File option list >> From Folder option.

• As a result, the Browse window will open.
• Following, choose the folder where you have kept the CSV files.
• Subsequently, click on the Open button.

• As a result, the Power Query window will open and it will show you the CSV files to import.
• Subsequently, click on the Combine button >> Combine & Transform Data option.

• As a result, the Combine Files window will appear.
• You will see the preview here and choose the Delimiter option as Semicolon according to your data.
• Following, click on the OK button.

• Thus, you will get the merged file in the Power Query window.

• Last but not least, go to the File tab from the Power Query window >> click on the Close & Load button >> Close & Load option.

Consequently, you will get all the CSV files merged into a single sheet in Excel. And, the output should look like this.

Read More: How to Open CSV with Delimiter in Excel (6 Simple Ways)

💬 Things to Remember

• Maintain a specific delimiter for every file and every data.
• All CSV files should be oriented in the same way and from the same rows and columns.

Conclusion

So, in this article, I have shown you all steps to merge CSV files into multiple sheets in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!

Related Articles

• How to View CSV File in Excel (3 Effective Methods)
• Difference Between CSV and Excel Files (11 Suitable Examples)
• Convert CSV to Excel Automatically with Easy Steps
• [Solved:] Excel Is Opening CSV Files in One Column (3 Solutions)
• How to Convert CSV to XLSX Command Line (with Easy Steps)
• Excel VBA: Read a Text File Line by Line (6 Related Examples)