Extracting specific rows from several workbooks

[lovi]

Hello all,

Here is my situation: I need to extract some rows from several workbooks which are all saved in one folder. I would, for example, want to copy all rows that have column A=1 and column B=2 and paste it all into another workbook. Since it will be very tedious to do by hand, I wondering whether this could be done using a macro?

It would be one that

1.accesses every workbook in the folder,

2.copy the desired rows

3.paste them into the workbook where the macro is stored

4.repeat for each workbook without overwritting any rows in the 'master' workbook.


Any help will be really appreciated.


also, this could be used as a starting point since it is quite similar http://www.mrexcel.com/forum/showthread.php?t=555470


Thanks

Lovi

 

[Trevor G]

Here is 2 code samples one to open all workbooks in a folder and do soemthing which you can look to adapt.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Open_My_Files()<br><SPAN style="color:#007F00">'Open all workbooks and autofit all columns on all sheets</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MyFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> myPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>myPath = "M:\Access Files\" <SPAN style="color:#007F00">'Change path</SPAN><br>MyFile = Dir(myPath)<br><br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> MyFile <> ""<br><SPAN style="color:#00007F">If</SPAN> MyFile <SPAN style="color:#00007F">Like</SPAN> "*.xls" <SPAN style="color:#00007F">Then</SPAN><br>Workbooks.Open myPath & MyFile<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets<br>ws.Activate<br>Cells.EntireColumn.AutoFit<br><SPAN style="color:#00007F">Next</SPAN> ws<br>ActiveWorkbook.Close <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>MyFile = Dir<br><SPAN style="color:#00007F">Loop</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

With this one which searches down a column and where it finds something then copy it over to the another workbook, again you can adapt it.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> cpy()<br><SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")<br>    LR = .Range("K" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> LR<br>        <SPAN style="color:#00007F">If</SPAN> .Range("K" & i).Value = "Yes" <SPAN style="color:#00007F">Then</SPAN> .Rows(i).Copy Destination:=Workbooks.Open("M:\Access Files\tblStaff Import.xls").Sheets("Staff").Range("A" & Rows.Count).End(xlUp).Offset(1)<br>    <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>