Extract conditional Row from Files to New Workbook

kaffal

Board Regular
Joined
Mar 7, 2009
Messages
68
I receive a workbook that contains all the daily sales records of the sales personal everyday. In the workbook, the Sales record have been categorized into different product under different tab. All those daily workbooks are saved in one folder, C:\Sales\March\

I wish to combine all the sales records from the different files, into one new workbook instead of opening up and copy over. However I will only copy the specific row over if one the cell value in the row contain the value, "Captured". The macro will then copy and paste to the new workbook, Summary workbook under the same tabname, with the Sales workbook.

Thus in the Summary Workbook, I can view those row with Captured found in one of the cell, and categorized under the different tab name


Excel Workbook
ABCDEFGHIJKLMNO
114/3/2009
2NoProductProdut IDQtyPriceStart DateEnd DateNameTypeRemarksCustomer NameContactAddress
31prod11231002013/3/200920/3/2009PeterCapturedremark1Steve123456New York
42prod2123503014/3/200920/3/2009StevenGeneratedNilRave123456Mumbai
53prod31231002015/3/200920/3/2009MaryCapturedremark1Steve123456Hong Kong
64prod4123503016/3/200920/3/2009GaryCapturedremark1Kevin123456Tokyo
75prod51231003017/3/200920/3/2009PeterGeneratedNilKenichi123456London
Example of a daily Workbook (Sales Record) There are 30 such workbook in the folder




Summary Workbook, capturing only the row with "Captured" found in the row.
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td><td>N</td><td>O</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>No</td><td>Product</td><td>Produt ID</td><td>Qty</td><td>Price</td><td>Start Date</td><td>End Date</td><td>Name</td><td>Type</td><td>Remarks</td><td> </td><td>Customer Name</td><td>Contact </td><td>Address </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="text-align: right;">1</td><td>prod1</td><td style="text-align: right;">123</td><td style="text-align: right;">100</td><td style="text-align: right;">20</td><td style="text-align: right;">13/3/2009</td><td style="text-align: right;">20/3/2009</td><td>Peter</td><td>Captured</td><td>remark1</td><td> </td><td>Steve</td><td style="text-align: right;">123456</td><td>New York</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: right;">3</td><td>prod3</td><td style="text-align: right;">123</td><td style="text-align: right;">100</td><td style="text-align: right;">20</td><td style="text-align: right;">15/3/2009</td><td style="text-align: right;">20/3/2009</td><td>Mary</td><td>Captured</td><td>remark1</td><td> </td><td>Steve</td><td style="text-align: right;">123456</td><td>Hong Kong</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: right;">4</td><td>prod4</td><td style="text-align: right;">123</td><td style="text-align: right;">50</td><td style="text-align: right;">30</td><td style="text-align: right;">16/3/2009</td><td style="text-align: right;">20/3/2009</td><td>Gary</td><td>Captured</td><td>remark1</td><td> </td><td>Kevin</td><td style="text-align: right;">123456</td><td>Tokyo</td></tr></table>

 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,921
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top