The code below will open ALL files in the directory FOLDER. Then copy the
data on Sheet1 E1:I1 to the next row in the workbook where the macro is run.
Change FOLDER and sheet Names as required.


Sub GetData()

Folder = "C:\temp\"

Set NewSht = ThisWorkbook.ActiveSheet
RowCount = 1

FName = Dir(Folder & "*.xls")
Do While FName < ""
Set bk = Workbooks.Open(Filename:=Folder & FName)
bk.Sheets("Sheet1").Range("E1:I1").Copy _
Destination:=NewSht.Range("E" & RowCount)
bk.Close savechanges:=False
RowCount = RowCount + 1
FName = Dir()
Loop
End Sub

"TFMR" wrote:

Hi All,

I have 50 excel files in folder I want to copy e1,f1,g1,h1,i1 to one new
file against file name. In new excel file in a column files name until 50 and
e1 the first file's data, e2 to i2 2nd files data and so on till 50 files.

Thanks