Sub LoopThroughFiles()

            Dim StrFile As String
            StrFile = Dir("c:\testfolder\*test*")
            
            Do While Len(StrFile) > 0
              Debug.Print StrFile
              StrFile = Dir
            Loop

        End Sub
            

        Sub LoopThroughFiles()
        Application.ScreenUpdating = False

        Dim myPath As String
        Dim StrFile As String
        Dim myTempWB As Workbook
        
        myPath = "C:\Users\myFolder\"
        StrFile = Dir(myPath)
        
        Do While Len(StrFile) > 0
          Set myTempWB = Workbooks.Open(Filename:=myPath + StrFile, ReadOnly:=True)
          
          With ThisWorkbook.Worksheets("Sheet1").Cells(1, 1)
            .End(xlDown).End(xlToRight).Offset(1, 1).Value = StrFile
            myTempWB.Worksheets(1).Cells(1, 1).CurrentRegion.Copy .End(xlDown).Offset(1, 0)
            
          End With
          
          myTempWB.Close
          Set myTempWB = Nothing
          
          StrFile = Dir
        Loop

        Application.ScreenUpdating = True

        End Sub