I have a macro below that will merge all the xls files in a directory
into one excel file. What I want is a slight addition to my macro to
bring into cell A1 the filename of each so that I can show a clear
distinction between the files being merged into one sheet.

Can someone help with this small change?

Kind Regards,

Stuart

Sub CombineFiles()
Dim TargetSht As Worksheet
Dim i As Integer
Dim wks As Worksheet


Application.ScreenUpdating = False


Set TargetSht = ThisWorkbook.ActiveSheet


With Application.FileSearch
.NewSearch
.LookIn = "I:\CDF Test\excel\test"
.SearchSubFolders = False
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s)
found."
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
For Each wks In ActiveWorkbook.Worksheets
wks.UsedRange.Copy Destination:= _
TargetSht.Cells(Rows.Count, 1).End(xlUp).Offset(1,
0)


Next wks
ActiveWorkbook.Close False
Next i
Else
MsgBox "There were no files found."
End If
End With
Application.ScreenUpdating = True
End Sub