From the excellent Jerry Beaucaire; VBA, for use inside an Excel module.

https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/merge-functions/csvs-to-sheets

Sub ImportCSVs()
'Author:    Jerry Beaucaire
'Date:      8/16/2010
'Summary:   Import all CSV files from a folder into separate sheets
'           named for the CSV filenames
'
'Update:    2/8/2013   Macro replaces existing sheets if they already exist in master workbook
'Modded:    12/10/14   Minor edits (Jonathan Brickman)

Dim fPath       As String
Dim fCSV        As String
Dim SheetName   As String
Dim wbCSV       As Workbook
Dim wbMST       As Workbook

Set wbMST = ThisWorkbook
fPath = "X:\PATH-TO-CSVs\"       'path to CSV files, include the final \
Application.ScreenUpdating = False  'speed up macro
Application.DisplayAlerts = False   'no error messages, take default answers
fCSV = Dir(fPath & "*.csv")         'start the CSV file listing

    On Error Resume Next
    Do While Len(fCSV) > 0
        SheetName = Left(fCSV, Len(fCSV) - 4)   ' Trim .CSV from filename, make sheet name
        Set wbCSV = Workbooks.Open(fPath & fCSV)                    'open a CSV file
        wbMST.Sheets(ActiveSheet.Name).Delete                       'avoid replication, delete sheet
        ActiveSheet.Move After:=wbMST.Sheets(wbMST.Sheets.Count)    'move new sheet into Mstr
        ActiveSheet.Name = SheetName
        Columns.AutoFit             		'clean up display
        fCSV = Dir                  		'ready next CSV
    Loop

Application.ScreenUpdating = True
Set wbCSV = Nothing
End Sub