Try this

Use this macro then if the files are in C:\Data
More info here
http://www.rondebruin.nl/copy3.htm

Sub Example12()
   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

   'Fill in the path\folder where the files are
   'on your machine
   MyPath = "c:\Data"

   '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
   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

           ' You can use this if you want to copy only the values
           '        With ActiveSheet.UsedRange
           '            .Value = .Value
           '        End With

           mybook.Close savechanges:=False
       Next Fnum
   End If
CleanUp:
   Application.ScreenUpdating = True
End Sub