I wrote a macro, but I have to do to much manual work still........I have a file with two sheets:
Sheet1 is a store file with Store number in column A, UPC in column B and UPC Name in column C.
Sheet2 is a CORE list on a separate sheet, Column A is the product status, Column B is the UPC an Column C is the UPC Name.
I wrote the following macro, but I have to copy the create another sheet and list the CORE UPC in column A, the Store UPC in column B and the macro will return any Store UPC not in the Core UPC list in column C:
Code:
<TABLE style="WIDTH: 344pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=459><COLGROUP><COL style="WIDTH: 344pt; mso-width-source: userset; mso-width-alt: 16786" width=459><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 344pt; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=21 width=459>[FONT=Arial Unicode MS][SIZE=2]Sub WriteMissingUPC()[/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=21>[SIZE=2][FONT=Arial Unicode MS] Dim Rw As Long, Rw2 As Long[/FONT][/SIZE]</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=21>[SIZE=2][FONT=Arial Unicode MS] Dim iFound As Integer[/FONT][/SIZE]</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=21>[FONT=Arial Unicode MS][SIZE=2] [/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=21>[SIZE=2][FONT=Arial Unicode MS] Rw2 = 2[/FONT][/SIZE]</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=21>[SIZE=2][FONT=Arial Unicode MS] For Rw = 2 To Cells(Rows.Count, 1).End(xlUp).Row[/FONT][/SIZE]</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=21>[SIZE=2][FONT=Arial Unicode MS] iFound = WorksheetFunction.CountIf(Range("B:B"), Cells(Rw, 1))[/FONT][/SIZE]</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=21>[SIZE=2][FONT=Arial Unicode MS] If iFound = 0 Then[/FONT][/SIZE]</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=21>[SIZE=2][FONT=Arial Unicode MS] Cells(Rw2, 3).Value = Cells(Rw, 1).Value[/FONT][/SIZE]</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=21>[SIZE=2][FONT=Arial Unicode MS] Rw2 = Rw2 + 1[/FONT][/SIZE]</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=21>[SIZE=2][FONT=Arial Unicode MS] End If[/FONT][/SIZE]</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=21>[SIZE=2][FONT=Arial Unicode MS] Next Rw[/FONT][/SIZE]</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=21>[FONT=Arial Unicode MS][SIZE=2] [/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=21>[FONT=Arial Unicode MS][SIZE=2]End Sub[/SIZE][/FONT]</TD></TR></TBODY></TABLE>
This works fine for one store, and I have to copy the core UPC list and the store UPC list to a separate sheet everytime.
What I would like is to have the macro list the missing UPC's on a separate sheet called missingUPC, but I would like to see the following:
column A to list the store number(from Sheet1, columnA)
column b to list the UPC
column C to list the UPC name.
This would give a complete list of what I need while allowing me to import into this sheet multiple stores inventory to compare to the Core UPC list(which will not change much).
Any suggestions?
