I am working with a food inventory workbook. Let's call it inventory.xls. It has worksheets named for food categories such as meat, dairy, etc.
I need a macro that will scan a new worksheet (import.xls) with food items sorted by category and copy them to the appropriate worksheets in the INVENTORY Workbook, starting at row 8 on the existing sheet within inventory.xls
Below is an example of both worksheets.
inventory.xls
DAIRY
<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 56px"><col style="WIDTH: 209px"><col style="WIDTH: 73px"><col style="WIDTH: 35px"><col style="WIDTH: 71px"><col style="WIDTH: 73px"><col style="WIDTH: 81px"><col style="WIDTH: 54px"><col style="WIDTH: 30px"><col style="WIDTH: 30px"><col style="WIDTH: 52px"><col style="WIDTH: 48px"><col style="WIDTH: 54px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Todays Date:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]12/20/2010[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]Total:[/TD]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Date of Last Inventory:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]11/30/2010[/TD]
[TD="bgcolor: #c0c0c0, align: right"]$ - [/TD]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Days Since Last Inventory:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]20[/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Last Person to Inventory:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2"][/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="colspan: 3"][/TD]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #800000, align: center"]SUPC[/TD]
[TD="bgcolor: #800000, align: center"]Item Description[/TD]
[TD="bgcolor: #800000, align: center"]Brand[/TD]
[TD="bgcolor: #800000, align: center"]Count[/TD]
[TD="bgcolor: #800000, align: center"]Package Size[/TD]
[TD="bgcolor: #800000, align: center"]Mfg #[/TD]
[TD="bgcolor: #800000, align: center"]Re-order Count[/TD]
[TD="bgcolor: #800000, align: center"]Case Cost[/TD]
[TD="bgcolor: #800000, align: center"]Case[/TD]
[TD="bgcolor: #800000, align: center"]Each[/TD]
[TD="bgcolor: #003366, align: center"]Unit Cost [/TD]
[TD="bgcolor: #003366, align: center"]Units in Stock [/TD]
[TD="bgcolor: #003366, align: center"]Total $ [/TD]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #ccccff, align: center"]- [/TD]
[TD="bgcolor: #ccccff, align: right"][/TD]
[TD="bgcolor: #ccccff"]- [/TD]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0, align: center"]- [/TD]
[TD="bgcolor: #c0c0c0, align: right"][/TD]
[TD="bgcolor: #c0c0c0"]- [/TD]
</tbody>
import.xls
Sheet1
<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 48px"><col style="WIDTH: 48px"><col style="WIDTH: 48px"><col style="WIDTH: 48px"><col style="WIDTH: 48px"><col style="WIDTH: 48px"><col style="WIDTH: 48px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]8965881[/TD]
[TD="align: right"] 9[/TD]
[TD="align: right"]19.95[/TD]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]6267272[/TD]
[TD="align: right"]14.99[/TD]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]6697114[/TD]
[TD="align: right"] 4[/TD]
[TD="align: right"]43.61[/TD]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]2406163[/TD]
[TD="align: right"]12.98[/TD]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]8953028[/TD]
[TD="align: right"] 2[/TD]
[TD="align: right"]24.50[/TD]
[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]2006559[/TD]
[TD="align: right"]6.77[/TD]
[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]7234958[/TD]
[TD="align: right"] 3[/TD]
[TD="align: right"]75.25[/TD]
[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]9714023[/TD]
[TD="align: right"]22.27[/TD]
[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]8256323[/TD]
[TD="align: right"] 8[/TD]
[TD="align: right"]49.11[/TD]
[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]5020193[/TD]
[TD="align: right"] 2[/TD]
[TD="align: right"]20.83[/TD]
[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]5321906[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]10.39[/TD]
[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]0012104[/TD]
[TD="align: right"] 8[/TD]
[TD="align: right"]31.05[/TD]
[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]2105815[/TD]
[TD="align: right"] 1[/TD]
[TD="align: right"]16.99[/TD]
[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]2130060[/TD]
[TD="align: right"] 1[/TD]
[TD="align: right"]17.19[/TD]
[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]6631347[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]27.33[/TD]
[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: right"]0417527[/TD]
[TD="align: right"] 30[/TD]
[TD="align: right"]20.31[/TD]
[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: right"]2389534[/TD]
[TD="align: right"] 12[/TD]
[TD="align: right"]35.31[/TD]
[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: right"]1434729[/TD]
[TD="align: right"]10.60[/TD]
[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: right"]1073402[/TD]
[TD="align: right"] 1[/TD]
[TD="align: right"]56.42[/TD]
[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="align: right"]1070218[/TD]
[TD="align: right"] 1[/TD]
[TD="align: right"]39.88[/TD]
[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="align: right"]6632582[/TD]
[TD="align: right"] 6[/TD]
[TD="align: right"]66.66[/TD]
</tbody>
I need all of the items under the "DAIRY PRODUCTS" to write to a sheet in inventory.xls called "DAIRY" and so on with each category.
I can write a simple macro that will normalize the data into the correct columns before it is written to the new sheets.
Can anyone help me?
Dale
I need a macro that will scan a new worksheet (import.xls) with food items sorted by category and copy them to the appropriate worksheets in the INVENTORY Workbook, starting at row 8 on the existing sheet within inventory.xls
Below is an example of both worksheets.
inventory.xls
DAIRY
| A | B | C | D | E | F | G | H | I | J | K | L | M | |
<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 56px"><col style="WIDTH: 209px"><col style="WIDTH: 73px"><col style="WIDTH: 35px"><col style="WIDTH: 71px"><col style="WIDTH: 73px"><col style="WIDTH: 81px"><col style="WIDTH: 54px"><col style="WIDTH: 30px"><col style="WIDTH: 30px"><col style="WIDTH: 52px"><col style="WIDTH: 48px"><col style="WIDTH: 54px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Todays Date:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]12/20/2010[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]Total:[/TD]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Date of Last Inventory:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]11/30/2010[/TD]
[TD="bgcolor: #c0c0c0, align: right"]$ - [/TD]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Days Since Last Inventory:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]20[/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Last Person to Inventory:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2"][/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="colspan: 3"][/TD]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #800000, align: center"]SUPC[/TD]
[TD="bgcolor: #800000, align: center"]Item Description[/TD]
[TD="bgcolor: #800000, align: center"]Brand[/TD]
[TD="bgcolor: #800000, align: center"]Count[/TD]
[TD="bgcolor: #800000, align: center"]Package Size[/TD]
[TD="bgcolor: #800000, align: center"]Mfg #[/TD]
[TD="bgcolor: #800000, align: center"]Re-order Count[/TD]
[TD="bgcolor: #800000, align: center"]Case Cost[/TD]
[TD="bgcolor: #800000, align: center"]Case[/TD]
[TD="bgcolor: #800000, align: center"]Each[/TD]
[TD="bgcolor: #003366, align: center"]Unit Cost [/TD]
[TD="bgcolor: #003366, align: center"]Units in Stock [/TD]
[TD="bgcolor: #003366, align: center"]Total $ [/TD]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #ccccff, align: center"]- [/TD]
[TD="bgcolor: #ccccff, align: right"][/TD]
[TD="bgcolor: #ccccff"]- [/TD]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0, align: center"]- [/TD]
[TD="bgcolor: #c0c0c0, align: right"][/TD]
[TD="bgcolor: #c0c0c0"]- [/TD]
</tbody>
import.xls
Sheet1
| | A | B | C | D | E | F | G |
| DAIRY PRODUCTS | | | | | | | |
| | | | | | | | |
| BUTTERMILK 1% LOW FAT | / | .5 GAL | WHLFARM | ||||
| CHEESE AMER LOAF YEL ^ | Only | / | 5 LB | BBRLCLS | |||
| CHEESE AMER YEL 120 SLI ^ | / | 5 LB | BBRLCLS | ||||
| CHEESE CHDR SHRD YEL FCY MILD^ | Only | / | 5 LB | CASASOL | |||
| CHEESE COTTAGE SMALL CURD 2% ^ | / | 5 LB | WHLFARM | ||||
| CHEESE CREAM NEUFCH 1/3 LS FAT | Only | / | 3 LB | SYS IMP | |||
| CHEESE CUBE CHDR/COJCK/PEP JCK | / | 5# | BBRLIMP | ||||
| CHEESE PARM SHAVED | Only | / | 5 LB | BELGIO | |||
| CHEESE SWISS SLI | / | 1.5 LB | BBRLIMP | ||||
| CREAM SOUR CULTRD GRADE A ^ | / | 5 LB | WHLFARM | ||||
| CREAM SOUR PURE GRD A SQ PACKT | / | 1 OZ | DAISY | ||||
| CREAM WHIPPING HVY 40% FRSH | / | 32 OZ | WHLFARM | ||||
| EGG SHELL MED GR AA USDA WHT | / | 15 DZ | WHLFCLS | ||||
| ICE CREAM VAN | / | 3 GAL | WHLFREL | ||||
| MARGARINE SOFT BUTRY SPREAD | / | 5 GM | SMT BAL | ||||
| MARGARINE SOLID ALL VEG | / | 1 LB | WHLFCLS | ||||
| TOPPING WHPD IN BAG | / | 16 OZ | WHLFCLS | ||||
| MEATS | | | | | | | |
| | | | | | | | |
| BACON END & PIECES | Only | / | 3 LB | FARMLND | |||
| BACON LAYFLAT 18/22 CT ^ | / | 15 LB | SYS REL | ||||
| BACON SLAB SLI 18/22CT TX SMK^ | / | 15 LB | SYS REL | ||||
| BEEF CORNED SLI | / | 2 LB | HORMEL |
<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 48px"><col style="WIDTH: 48px"><col style="WIDTH: 48px"><col style="WIDTH: 48px"><col style="WIDTH: 48px"><col style="WIDTH: 48px"><col style="WIDTH: 48px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]8965881[/TD]
[TD="align: right"] 9[/TD]
[TD="align: right"]19.95[/TD]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]6267272[/TD]
[TD="align: right"]14.99[/TD]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]6697114[/TD]
[TD="align: right"] 4[/TD]
[TD="align: right"]43.61[/TD]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]2406163[/TD]
[TD="align: right"]12.98[/TD]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]8953028[/TD]
[TD="align: right"] 2[/TD]
[TD="align: right"]24.50[/TD]
[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]2006559[/TD]
[TD="align: right"]6.77[/TD]
[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]7234958[/TD]
[TD="align: right"] 3[/TD]
[TD="align: right"]75.25[/TD]
[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]9714023[/TD]
[TD="align: right"]22.27[/TD]
[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]8256323[/TD]
[TD="align: right"] 8[/TD]
[TD="align: right"]49.11[/TD]
[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]5020193[/TD]
[TD="align: right"] 2[/TD]
[TD="align: right"]20.83[/TD]
[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]5321906[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]10.39[/TD]
[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]0012104[/TD]
[TD="align: right"] 8[/TD]
[TD="align: right"]31.05[/TD]
[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]2105815[/TD]
[TD="align: right"] 1[/TD]
[TD="align: right"]16.99[/TD]
[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]2130060[/TD]
[TD="align: right"] 1[/TD]
[TD="align: right"]17.19[/TD]
[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]6631347[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]27.33[/TD]
[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: right"]0417527[/TD]
[TD="align: right"] 30[/TD]
[TD="align: right"]20.31[/TD]
[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: right"]2389534[/TD]
[TD="align: right"] 12[/TD]
[TD="align: right"]35.31[/TD]
[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: right"]1434729[/TD]
[TD="align: right"]10.60[/TD]
[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: right"]1073402[/TD]
[TD="align: right"] 1[/TD]
[TD="align: right"]56.42[/TD]
[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="align: right"]1070218[/TD]
[TD="align: right"] 1[/TD]
[TD="align: right"]39.88[/TD]
[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="align: right"]6632582[/TD]
[TD="align: right"] 6[/TD]
[TD="align: right"]66.66[/TD]
</tbody>
I need all of the items under the "DAIRY PRODUCTS" to write to a sheet in inventory.xls called "DAIRY" and so on with each category.
I can write a simple macro that will normalize the data into the correct columns before it is written to the new sheets.
Can anyone help me?
Dale
