Inventory Control help....

C

chefdt

Board Regular
Joined
Jul 1, 2008
Messages
163
[h=2][/h] <ins style="display:inline-table;border:none;height:60px;margin:0;padding:0;position:relative;visibility:visible;width:234px;background-color:transparent"><ins id="aswift_0_anchor" style="display:block;border:none;height:60px;margin:0;padding:0;position:relative;visibility:visible;width:234px;background-color:transparent"></ins></ins>
I have a WORKBOOK named "Inventory". It consists of several WORKSHEETS that divide my food inventory into categories such as "MEAT", "DAIRY PRODUCTS", etc.

I recorded a macro that will import my inventory from an online ordering system, dump it to a WORKSHEET named "IMPORT" and normalize the data.

I want to create a macro that looks through the import data and parses it into the appropriate WORKSHEET.

Column "I" of the "IMPORT" sheet contains the name of the WORKSHEET that the data needs to be copied to.

The other worksheets will already have data in them, starting at "A8". As each item is copied to the appropriate sheet, I want to verify if the item already exists on the sheet and do one of two options.....A) If it exists, copy only "H" which is the case cost, and flag by color or bold text if the new price is different from the current B) If it doesn't exist, add the entire row of data to the bottom row of data in the appropriate sheet.

Here is a sample of "IMPORT" and "DAIRY"

Dairy Products

[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #cacaca"]
[TD][/TD]
[TD]A[/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]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD][/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Todays Date:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]10/31/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]Total:[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD][/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Date of Last Inventory:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]3/12/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]$ -[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD][/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Days Since Last Inventory:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: center"]1329[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD][/TD]
[TD="bgcolor: #c0c0c0, colspan: 2, align: right"]Last Person to Inventory:[/TD]
[TD="bgcolor: #c0c0c0, colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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: #969696, align: center"]Unit Cost[/TD]
[TD="bgcolor: #969696, align: center"]Units in Stock[/TD]
[TD="bgcolor: #969696, align: center"]Total $[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]8965881[/TD]
[TD]BUTTERMILK 1% LOW FAT[/TD]
[TD="align: left"]WHLFARM[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"].5 GAL[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$ 19.05[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #ccccff, align: center"]$ 2.12[/TD]
[TD="bgcolor: #ccccff, align: right"][/TD]
[TD="bgcolor: #ccccff, align: right"]$ -[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #d9d9d9, align: center"]6697890[/TD]
[TD]CHEESE AMER YEL 160 SLI[/TD]
[TD="align: left"]BBRLCLS[/TD]
[TD="align: center"]4[/TD]
[TD="bgcolor: #d9d9d9, align: center"]5 LB[/TD]
[TD="bgcolor: #d9d9d9"][/TD]
[TD="bgcolor: #d9d9d9"][/TD]
[TD="bgcolor: #d9d9d9, align: right"]$ 37.68[/TD]
[TD][/TD]
[TD="bgcolor: #d9d9d9"][/TD]
[TD="bgcolor: #d9d9d9, align: center"]$ 9.42[/TD]
[TD="bgcolor: #d9d9d9, align: right"][/TD]
[TD="bgcolor: #d9d9d9, align: right"]$ -[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]2404135[/TD]
[TD]CHEESE CHDR YEL MILKD SHRD FTHR[/TD]
[TD="align: left"]CASASOL[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5 LB[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$ 45.80[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$ 11.45[/TD]
[TD="bgcolor: #ccccff, align: right"][/TD]
[TD="bgcolor: #ccccff, align: right"]$ -[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="bgcolor: #d9d9d9, align: center"]7234958[/TD]
[TD="bgcolor: #d9d9d9"]CHEESE CUBE CHDR/COJCK/PEP JCK[/TD]
[TD="bgcolor: #d9d9d9, align: left"]BBRLIMP[/TD]
[TD="bgcolor: #d9d9d9, align: center"]3[/TD]
[TD="bgcolor: #d9d9d9, align: center"]5 #[/TD]
[TD="bgcolor: #d9d9d9"][/TD]
[TD][/TD]
[TD="bgcolor: #d9d9d9, align: right"]$ 48.00[/TD]
[TD="bgcolor: #d9d9d9"][/TD]
[TD="bgcolor: #d9d9d9"][/TD]
[TD="align: center"]$ 16.00[/TD]
[TD="bgcolor: #d9d9d9, align: right"][/TD]
[TD="bgcolor: #d9d9d9, align: right"]$ -[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]4791386[/TD]
[TD]CHEESE CUBE HAVARTI/GOUDA/MUFN[/TD]
[TD="align: left"]SCHRBER[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5 LB[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$ 46.74[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$ 15.58[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #ccccff, align: right"]$ -[/TD]
[/TR]
</tbody>[/TABLE]



Import

[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #cacaca"]
[TD][/TD]
[TD]A[/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]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: left"]SUPC[/TD]
[TD]Desc[/TD]
[TD]Brand[/TD]
[TD="align: center"]Pack[/TD]
[TD="align: center"]Size[/TD]
[TD="align: center"]Mfr #[/TD]
[TD="align: center"]Par[/TD]
[TD]Case $[/TD]
[TD]Cat[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]DAIRY PRODUCTS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]0671677[/TD]
[TD]CHEESE BLUE CRUMBLES[/TD]
[TD]SYS IMP[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5 LB[/TD]
[TD="align: center"]10000847[/TD]
[TD][/TD]
[TD="align: right"]$ 12.59[/TD]
[TD]Dairy Products[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]5469259[/TD]
[TD]CHEESE BRIE DOMSTC 1 KG[/TD]
[TD]BBRLIMP[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2.2 LB[/TD]
[TD="align: center"]10007830[/TD]
[TD][/TD]
[TD="align: right"]$ 17.28[/TD]
[TD]Dairy Products[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]2406189[/TD]
[TD]CHEESE CHDR JACK SHRD FCY[/TD]
[TD]CASASOL[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5 LB[/TD]
[TD="align: center"]10000253[/TD]
[TD][/TD]
[TD="align: right"]$ 47.78[/TD]
[TD]Dairy Products[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]2599793[/TD]
[TD]CHEESE CHDR MILD YEL PRNT[/TD]
[TD]BBRLIMP[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]10 LB[/TD]
[TD="align: center"]10000253[/TD]
[TD][/TD]
[TD="align: right"]$ 23.70[/TD]
[TD]Dairy Products[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]6159263[/TD]
[TD]CHEESE CHDR SHRP PRNT YEL[/TD]
[TD]BBRLIMP[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]10 LB[/TD]
[TD="align: center"]10000253[/TD]
[TD][/TD]
[TD="align: right"]$ 27.32[/TD]
[TD]Dairy Products[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: left"]2357598[/TD]
[TD]CHEESE CHDR SMKD[/TD]
[TD]TILAMOK[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]2 LB[/TD]
[TD="align: center"]10007703[/TD]
[TD][/TD]
[TD="align: right"]$ 61.70[/TD]
[TD]Dairy Products[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: left"]2357580[/TD]
[TD]CHEESE CHDR XSHRP WHITE[/TD]
[TD]TILAMOK[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5 LB[/TD]
[TD="align: center"]10007703[/TD]
[TD][/TD]
[TD="align: right"]$ 53.95[/TD]
[TD]Dairy Products[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: left"]2220143[/TD]
[TD]CHEESE FETA CRUMBLE DOM[/TD]
[TD]SYS IMP[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5 LB[/TD]
[TD="align: center"]10000847[/TD]
[TD][/TD]
[TD="align: right"]$ 36.21[/TD]
[TD]Dairy Products[/TD]
[/TR]
</tbody>[/TABLE]


Any help appreciated!​
 
Sort by date Sort by votes
Comfy said:
Ahh I missed that out.

Rich (BB code): 
If SUPCMatch Is Nothing Then
                        ws1.Range(Cells(i, 1), Cells(i, 8)).Copy
                        ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll
                    Else
                        If ws1.Cells(i, CaseCol).Value <> SUPCMatch.Offset(0, 7).Value Then
                            With SUPCMatch.Offset(0, 7).Interior
                                .Pattern = xlSolid
                                .ThemeColor = xlThemeColorLight1
                                .PatternColorIndex = xlAutomatic
                            End With
                            With SUPCMatch.Offset(0, 7).Font
                                .ThemeColor = xlThemeColorDark1
                                .Bold = True
                            End With
.Value = ws1.Cells(i, CaseCol).Value
                        End If
                    End If

add that line.
Click to expand...

Comes up with "invalid or unqualified reference".
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
My bad I mis-read what I had written :confused:

Code: 
If SUPCMatch Is Nothing Then
                        ws1.Range(Cells(i, 1), Cells(i, 8)).Copy
                        ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll
                    Else
                        If ws1.Cells(i, CaseCol).Value <> SUPCMatch.Offset(0, 7).Value Then
                            With SUPCMatch.Offset(0, 7).Interior
                                .Pattern = xlSolid
                                .ThemeColor = xlThemeColorLight1
                                .PatternColorIndex = xlAutomatic
                            End With
                            With SUPCMatch.Offset(0, 7).Font
                                .ThemeColor = xlThemeColorDark1
                                .Bold = True
                            End With
SUPCMatch.Offset(0, 7).Value = ws1.Cells(i, CaseCol).Value
                        End If
                    End If
 
Upvote 0
Nevermind.....Got it.

Changed to

SUPCMatch.Offset(0, 7).Value = ws1.Cells(i, CaseCol).Value

It works. Thanks for all your help.


Dale
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Inventory Maco help needed.....
Replies
0
Views
596
chefdt
C
C
  • Question Question
Copy from one worksheet to another based on cell value...
Replies
8
Views
1K
chefdt
C
C
  • Question Question
Import data for Inventory
Replies
0
Views
584
chefdt
C
C
  • Question Question
Inventory Macro - Copy rows to new worksheets
Replies
0
Views
630
chefdt
C

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

Share this page

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