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.

Sample Dataset to Merge CSV Files into Multiple Sheets

Step 1 – Open a VBA Module

Go to the Developer tab and select Visual Basic.

Access the Visual Basic Tool

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

Insert a New Module to Merge CSV Files into Multiple Sheets

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.

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

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

Microsoft Excel Dialogue Box

The Save As window will appear.
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

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.

Access the Macros Tool

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

Run Macro to Merge CSV Files into Multiple Sheets

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

Merged CSV Files into Multiple Sheets

Go to the File tab.

Access the File Tab

Click on the Save As option.

Choose the Save As Option

Click on the Browse option.

Excel Save As Window

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

Save As Dialogue Box

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

Merged CSV Files into Multiple Sheets

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.

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

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

Choose Folder Containing CSV Files

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

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

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

Combine Files Window