I am trying to find an automated solution for the following daily task. I have a master workbook with 13 sheets.
Sheet names are Jan-Dec (all 12 months) and Data.
Every sheet has 2 sets of 3 columns: Item Code (A1), Year (B1), Price (C1) and Item Code (E1), Year (F1), Price (G1).
Every day I have over 1000 new entries in "Data" sheet and then have to find matching item code (in Column A) in other 12 sheets, columns A-C, cut and move new matching data to E-G and highlight the new entries.
I have tried the following vba codes:
and also the following one, which works perfectly:
I'm trying to improving the workbook and add some modification to above VBA as following:
1- In my monthly sheets now I have 2 sets of columns as you can see in picture.
As you can see in first set of columns I have 8 titles A-H (REPORT # | DATE | TRANSACTION ID | ITEM CODE | YEAR | PRICE | NOTES | SOLD BY), and in second set I have 4 titles J-M (REPORT # | ITEM CODE | YEAR | PRICE).
2- In DATA sheet I have 1 set of columns with 4 titles A-D (REPORT # | ITEM CODE | YEAR | PRICE). See the picture.
Improvements I'm looking to make:
1- If finds matches in month tabs (cells A:H) for cells B:C in "DATA" sheet, move (cut) cells A:D from "DATA" to matching month's cells J:M and highlight it yellow.
2- If finds 2 or more matches, first try to move it to the first match, but in case if the first one already has a matching data, move it to the second one.
3- If finds only one match and already there is a data in cells J:M, insert a row below, add data and highlight it blue.
4- If no match finds, highlight cells red in "DATA" tab.
I attached a sample page of my workbook.
Hope someone can help with this improvement.
Cross posted: here, here and here
Bookmarks