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

Get FREE Advanced Excel Exercises with Solutions!

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.

Sample Dataset to Merge CSV Files into Multiple 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.

Access the Visual Basic Tool

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

Insert a New Module to Merge CSV Files into Multiple Sheets

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.

Write and Save the Required VBA Code to Merge CSV Files into Multiple Sheets

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

Microsoft Excel Dialogue Box

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

Save the Excel File as .xlsm File to Merge CSV Files into Multiple Sheets

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


πŸ“Œ 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.

Access the 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.

Run Macro to Merge CSV Files into Multiple Sheets

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

Merged CSV Files into Multiple Sheets

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

Access the File Tab

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

Choose the Save As Option

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

Excel Save As Window

  • 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.

Save As Dialogue Box

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

Merged CSV Files into Multiple Sheets

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.

Access the Power Query Tool to Merge CSV Files into a Single Sheet

  • 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.

Choose Folder Containing CSV Files

  • 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.

Choose the Combine & Transform Data Option to Merge CSV Files into a Single Sheet

  • 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.

Combine Files Window

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

Merged CSV Files in 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.

Choose the Close & Load Option to Merge CSV Files into a Single Sheet

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

Merged CSV Files into a Single Sheet

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

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo