macro for opening all excel files and copying contents

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..

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
>
>

Thanks Tom ..this was really helpfull..now i can modify as per my requirement..

Originally Posted by Tom Ogilvy

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
>
>