I managed to get where I can select a single csv file and place the data on a worksheet, but I'm stumbling on how to select multiple csv files and consolidate to the same worksheet.
Below is my current code, and yes, a lot of help came from this forum. 

Option Explicit
Dim fName1 As Variant    'AS9102 or MOT workbook
Dim fName2 As Variant    'csv workbook to collect data from

Sub MCosomosGetCSV_File()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Sheets("Sheet3").Unprotect ("2008")
    Sheets("Sheet4").Activate

    'MsgBox Left(Sheets("Form1").Range("A1"), 10) 'for testing
    If Left(Sheets("Sheet1").Range("A1"), 10) = "SAE AS9102" Or _
       Left(ActiveWorkbook.Name, 3) = "MOT" Then

        fName1 = ActiveWorkbook.Name
        fName2 = Application.GetOpenFilename(FileFilter:="All files (*.csv), *.csv", Title:="Please open the CSV file")
        If fName2 = "False" Then
            MsgBox "You have not selected a file."
            Exit Sub
        Else
            Workbooks.Open fileName:=fName2
            fName2 = ActiveWorkbook.Name
        End If
        
        With fName2
            Cells.Select
            Selection.Copy
            Windows(fName1).Activate
            Cells.Select
            ActiveSheet.Paste
            Range("A1").Select
        End With
        Workbooks(fName2).Close
    End If

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
Any hints, tips or examples is appreciated.