gloriag
03-11-2014, 12:15 AM
I'm not familiar with VBA. I tried to search for a code to perform the task below, unfortunately I haven’t able to find the code in my searches. Please help me.
I have two workbooks (WB1 is attached). I would like to create a macro that will do the following:-
loop through the column named “Code” in WB1 and search for a match for the cell in the column “Inventory ID” of WB2. If it finds a match, it will copy the corresponding value in the column “Total” of WB2 to the column “Apple” in WB1. For example, the value of apple of the code ABC in WB1 should be changed to “3534.00”.
for those “Inventory ID” of WB2 that doesn’t match the “Code” in WB1, it will copy both “Inventory ID” and the corresponding value to the end of the “Code” and “Apple”.
WB2 is a pivot table as below
Sum of Inventory Value
Inventory ID
Total
ABC
3,534.00
DEF
12,276.00
FRE
8,607.00
GHI
3,248.00
IN0001
1,275.00
IN0005
4,650.00
IN0008
3,838.00
IN0009
7,198.00
IN0012
396.00
IN0013
1,872.00
IN0014
2,604.00
IN0016
8,640.00
IN0017
5,529.00
IN0018
72.00
IN0019
11,726.00
IN0020
1,984.00
IN0021
2,128.00
IN0022
4,368.00
IN0023
3,074.00
IN0024
12,975.00
IN0025
392.00
LOK
55.00
MIL
1,472.00
MKO
8,750.00
MNO
10,384.00
Grand Total
121,047.00
I have two workbooks (WB1 is attached). I would like to create a macro that will do the following:-
loop through the column named “Code” in WB1 and search for a match for the cell in the column “Inventory ID” of WB2. If it finds a match, it will copy the corresponding value in the column “Total” of WB2 to the column “Apple” in WB1. For example, the value of apple of the code ABC in WB1 should be changed to “3534.00”.
for those “Inventory ID” of WB2 that doesn’t match the “Code” in WB1, it will copy both “Inventory ID” and the corresponding value to the end of the “Code” and “Apple”.
WB2 is a pivot table as below
Sum of Inventory Value
Inventory ID
Total
ABC
3,534.00
DEF
12,276.00
FRE
8,607.00
GHI
3,248.00
IN0001
1,275.00
IN0005
4,650.00
IN0008
3,838.00
IN0009
7,198.00
IN0012
396.00
IN0013
1,872.00
IN0014
2,604.00
IN0016
8,640.00
IN0017
5,529.00
IN0018
72.00
IN0019
11,726.00
IN0020
1,984.00
IN0021
2,128.00
IN0022
4,368.00
IN0023
3,074.00
IN0024
12,975.00
IN0025
392.00
LOK
55.00
MIL
1,472.00
MKO
8,750.00
MNO
10,384.00
Grand Total
121,047.00