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


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

macro for opening all excel files and copying contents
 

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

macro for opening all excel files and copying contents
 

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

Tom Ogilvy Wrote:
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




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