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

"Stuart" ha scritto nel messaggio
oups.com...
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?


I'm not sure if I've understood correctly but you can try to insert this
line in the routine:

............
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
'****************START NEW LINE***********************
TargetSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = "--
" & .FoundFiles(i) & " <--"
'****************END NEW LINE***********************
For Each wks In ActiveWorkbook.Worksheets
..... ......


Bye

FM