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.

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

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.

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.