VBA

Option Explicit
Sub ImportFiles()
    Dim MyPath As String
    Dim FilesInPath As String
    Dim MyFiles() As String
    Dim SourceRcount As Long
    Dim Fnum As Long
    Dim mybook As Workbook
    Dim basebook As Workbook
    Dim folderPath As String
    Dim strwsNmae As String
    folderPath = Application.ActiveWorkbook.Path

    MyPath = folderPath
     'Add a slash at the end if the user forget it
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If
     'If there are no Excel files in the folder exit the sub
    FilesInPath = Dir(MyPath & "*.csv")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If
     'On Error GoTo CleanUp
    Application.ScreenUpdating = False
    Set basebook = ThisWorkbook
     'Fill the array(myFiles)with the list of Excel files in the folder
    Redim Preserve MyFiles(1 To 6)
    For Each strwsNmae In VBA.Array("alarm_comparison*", "dtsmom_comparison*", "genmom_comparison*", "mapboard_comparison*", "scadamom_comparison*", "netmom_comparison*")
        Fnum = Fnum + 1
        MyFiles(Fnum) = NewestFile(MyPath, strwsNmae & ".csv")
    Next
     '  Fnum = 0
     ' Do While FilesInPath <> ""
     '     Fnum = Fnum + 1
     '    ReDim Preserve MyFiles(1 To Fnum)
     '     MyFiles(Fnum) = FilesInPath
     '    FilesInPath = Dir()
     ' Loop
     'Loop through all files in the array(myFiles)

    If Fnum > 0 Then
        For Fnum = LBound(MyFiles) To UBound(MyFiles)
            Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
            mybook.Worksheets(1).Copy after:= _
            basebook.Sheets(basebook.Sheets.Count)
            On Error Resume Next
            ActiveSheet.Name = mybook.Name
            On Error Goto 0

            mybook.Close savechanges:=False
        Next Fnum
    End If
     'CleanUp:
    Application.ScreenUpdating = True
End Sub
Function NewestFile(Directory, FileSpec)
     'John Walkenback
    Dim FileName As String
    Dim MostRecentFile As String
    Dim MostRecentDate As Date
    If Right(Directory, 1) <> "\" Then Directory = Directory & "\"

    FileName = Dir(Directory & FileSpec, 0)
    If FileName <> "" Then
        MostRecentFile = FileName
        MostRecentDate = FileDateTime(Directory & FileName)
        Do While FileName <> ""
            If FileDateTime(Directory & FileName) > MostRecentDate Then
                MostRecentFile = FileName
                MostRecentDate = FileDateTime(Directory & FileName)
            End If
            FileName = Dir
        Loop
    End If
    NewestFile = MostRecentFile
End Function