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
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>
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 | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | 14/3/2009 | ||||||||||||||||
| 2 | No | Product | Produt ID | Qty | Price | Start Date | End Date | Name | Type | Remarks | Customer Name | Contact | Address | ||||
| 3 | 1 | prod1 | 123 | 100 | 20 | 13/3/2009 | 20/3/2009 | Peter | Captured | remark1 | Steve | 123456 | New York | ||||
| 4 | 2 | prod2 | 123 | 50 | 30 | 14/3/2009 | 20/3/2009 | Steven | Generated | Nil | Rave | 123456 | Mumbai | ||||
| 5 | 3 | prod3 | 123 | 100 | 20 | 15/3/2009 | 20/3/2009 | Mary | Captured | remark1 | Steve | 123456 | Hong Kong | ||||
| 6 | 4 | prod4 | 123 | 50 | 30 | 16/3/2009 | 20/3/2009 | Gary | Captured | remark1 | Kevin | 123456 | Tokyo | ||||
| 7 | 5 | prod5 | 123 | 100 | 30 | 17/3/2009 | 20/3/2009 | Peter | Generated | Nil | Kenichi | 123456 | London | ||||
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>
