Forum Discussion
macro to insert lines when comparing 2 lists or form to update 2 lists
Hello,
i've currently got 2 lists :
- list 1 is a catalog of tools
- list 2 is a the tracking of orders from the catalog
my aim is to declare that there has been an order in the the catalog (list 1) by putting a "Y" in a column and then running a macro that will add the new lines into the list 2 without deleting the old ones and the sorting by "internal ref"
both lists are in the same work book just different pages
My main question is how can i update list 2 with out deleting the lines currently present, nor creating duplicates ?
i was thinking about going ahead this way :
1 - fetch last line of list 2
2 - copy/paste or advanced filter to fetch all the orders
3 - sort by internal ref
but that doesn't solve the duplicate error ... maybe it could be done with an "if" or ticking "unique records only" in the advanced filter?
if this option was to complicated i was thinking of a "place order" form that would update list 1 and then add the affected lines to list 2 ... the form would need to be able to place multiple orders at once
list 1 looks like this :
| Tool name | tool version | Internal ref | Tool price | Odered by X | Ordered by YZ |
| Tool 1 | A | Tool 1_ind A | 99 | ||
| Tool 1 | B | Tool 1_ind B | 56 | Y | |
| Tool 1 | C | Tool 1_ind C | 7 | Y | |
| Tool 2 | A | Tool 2_ind A | 456 | Y | Y |
| tool 3 | A | tool 3_ind A | 456 | Y | Y |
| Tool 4 | A | Tool 4_ind A | 46 | Y | |
List 2 looks like (once filtered by internal ref):
| Internal ref | Tool name | tool version | Client | internal price | internal order date | internal delivery date | comments | client ship date |
| Tool 1_ind B | Tool 1 | B | Y | |||||
| Tool 1_ind C | Tool 1 | C | X | |||||
| Tool 2_ind A | Tool 2 | A | X | |||||
| Tool 2_ind A | Tool 2 | A | Y | |||||
| tool 3_ind A | tool 3 | A | X | |||||
| tool 3_ind A | tool 3 | A | Y | |||||
| Tool 4_ind A | Tool 4 | A | X | |||||
1 Reply
If you wish to create a form for placing orders that updates List 1 and adds rows to List 2, you could use a UserForm in VBA, but that requires a more elaborate setup. Let me know if you’re interested in that, and I can guide you through that process as well!
