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

We 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. We want to merge all the workbooks in a single workbook in multiple sheets.

Step 1 – Open a VBA Module

• Go to the Developer tab and select Visual Basic.

• The VBA Editor window will open.
• Go to the Insert tab and choose the Module option.

Read More: How to Keep Leading Zeros in Excel CSV Programmatically

Step 2 – Insert the VBA Code

• Click on Module 1.
• Insert the following code in window.

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

• Press Ctrl + S on your keyboard to save the VBA code.

• A Microsoft Excel dialogue box will appear.
• Click on the No option.

• The Save As window will appear.
• Change the Save as type: option as .xlsm format and click on the Save button.

Read More: How to Fix CSV File in Excel

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

• Go to the Developer tab and choose Macros.

• The Macro window will appear.
• Choose the MergeCSVFiles macro from the Macros pane and click on the Run button.

• All the workbooks will be merged into a new workbook as different worksheets.

• Go to the File tab.

• Click on the Save As option.

• Click on the Browse option.

• The Save As dialog box will appear.
• Insert your desired file name in the File name: text box and click on the Save button.

• The CSV files are merged into multiple sheets of a single workbook.

Read More: Paste Comma Separated Values into Excel

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

Steps:

• Go to the Data tab and select Get Data.
• Choose From File and select the From Folder option.

• The Browse window will open.
• Choose the folder where you have kept the CSV files.
• Click on the Open button.

• The Power Query window will open and show the CSV files to import.
• Click on Combine and select the Combine & Transform Data option.

• The Combine Files window will appear.
• Choose the Delimiter as Semicolon (or change depending on what your data is delimited by).
• Click on OK.

• You will get the merged file in the Power Query window.

• Go to the File tab from the Power Query window.
• Click on Close & Load and select Close & Load.

• You will get all the CSV files merged into a single sheet in Excel. The output should look like this.

Read More: How to Edit CSV File in Excel

Things to Remember

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

Download the Practice Workbook

CSV Source Files:

Final Merged File:

Related Articles

• How to Sort CSV File in Excel
• How to Merge CSV Files in Excel
• Formatting CSV File in Excel
• How to Stop Excel from Auto Formatting Dates in CSV

<< Go Back to Merge CSV Files in Excel | Import CSV to Excel | Importing Data in Excel | Learn Excel