Sub ConsolidateData()
Dim bk as Workbook, bk1 as Workbook
Dim sh as Worksheet
Dim sPath as String, sName as String
Dim rw as Long
sPath = "C:\Myfolder\"
sName = Dir(sPath & "*.xls")
set bk = ActiveWorkbook
set sh = bk.Worksheets.Add(After:=bk.Worksheets( _
bk.worksheets.count))
rw = 1
do while sName < ""
set bk1 = workbooks.open(sPath & sName)
bk1.worksheets(1).Range("1:5").copy sh.Cells(rw,1)
rw = rw + 5
bk1.close Savechanges:=False
sName = dir
Loop
End sub

should be pretty close to what you want. Adjust as appropriate

--
Regards,
Tom Ogilvy



"roshinpp_77" wrote:


could anyone help me write macro to open allexcel files from a folder
and copy certain rows and paste all details in a new excel sheet line
by line.


Thanks..


--
roshinpp_77
------------------------------------------------------------------------
roshinpp_77's Profile: http://www.excelforum.com/member.php...o&userid=34924
View this thread: http://www.excelforum.com/showthread...hreadid=546652