VBA copy data from sheet1 to sheet2 using cross-reference table

[Old-One]

I need to transfer data (values) from Sheet1, listed as Stock #’s to Sheet2, listed as Inventory #’s, using the cross-reference table on Sheet3.


The number of columns of data in each row could range from around 10 columns to about 50 columns, so it would be nice if the VBA could copy from the first cell to the right of the Stock # to the last cell containing a value and paste that into Sheet2. There are also blank cells in each row.

I tried to use FIND, to make it process quickly since there are hundreds of rows of data in the actual spreadsheets but could not figure how to reference the tables on Sheet3.


It seems simple enough but I can’t seem to make it work. Maybe, I need to give up trying to use FIND and use something else, but I’m not sure what. Any assistance would be greatly appreciated.

Sheet1 – Original Data

Stock #	1/28/2012	2/28/2012	3/28/2012	4/28/2012	5/28/2012	6/28/2012
3768LK	1.89	1.73	1.98	1.57	2.03
KJ 3-478	2.13		2.98	1.88	1.67
M-y78	0.98	1.06	1.22		3.76	1.87
tt-67	1.03	2.28	1.87		1.35	1.47
9-7-HE	2.28	2.66	1.89	1.21	3.56	2.95
8-PM-9	2.80			3.78	2.22	2.73

<tbody>
</tbody>

Sheet2 – Need to transfer data from Sheet 1 to Sheet 2 using cross-reference table (Sheet3)

Inventory #	1/28/2012	2/28/2012	3/28/2012	4/28/2012	5/28/2012	6/28/2012
378-065-6
431-67-1
578-004-32
696-0235-1
727-34-34
886-563-3

<tbody>
</tbody>

Sheet3 – cross-reference from Stock # to Inv #

Stock #	Inventory #
3768LK	431-67-1
KJ 3-478	578-004-32
M-y78	378-065-6
tt-67	886-563-3
9-7-HE	727-34-34
8-PM-9	696-0235-1

<tbody>
</tbody>

Thank you for your assistance.