copy data to one file from all files

T

TFMR

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
 
J

Joel

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
 
T

TFMR

Thanks Ron de Bruin,

that is working fine.

Can u please help me for following issue:-

As I mentioned before that I have around 50 files in folder and I want to
run excel macro on all files in folder and save them.

Kindly help me plz.

Thanks
 
Top