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
[TABLE="width: 459"]
<tbody>[TR]
[TD]Stock #[/TD]
[TD]1/28/2012[/TD]
[TD]2/28/2012[/TD]
[TD]3/28/2012[/TD]
[TD]4/28/2012[/TD]
[TD]5/28/2012[/TD]
[TD]6/28/2012[/TD]
[/TR]
[TR]
[TD]3768LK[/TD]
[TD]1.89[/TD]
[TD]1.73[/TD]
[TD]1.98[/TD]
[TD]1.57[/TD]
[TD]2.03[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KJ 3-478[/TD]
[TD]2.13[/TD]
[TD][/TD]
[TD]2.98[/TD]
[TD]1.88[/TD]
[TD]1.67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]M-y78[/TD]
[TD]0.98[/TD]
[TD]1.06[/TD]
[TD]1.22[/TD]
[TD][/TD]
[TD]3.76[/TD]
[TD]1.87[/TD]
[/TR]
[TR]
[TD]tt-67[/TD]
[TD]1.03[/TD]
[TD]2.28[/TD]
[TD]1.87[/TD]
[TD][/TD]
[TD]1.35[/TD]
[TD]1.47[/TD]
[/TR]
[TR]
[TD]9-7-HE[/TD]
[TD]2.28[/TD]
[TD]2.66[/TD]
[TD]1.89[/TD]
[TD]1.21[/TD]
[TD]3.56[/TD]
[TD]2.95[/TD]
[/TR]
[TR]
[TD]8-PM-9[/TD]
[TD]2.80[/TD]
[TD][/TD]
[TD][/TD]
[TD]3.78[/TD]
[TD]2.22[/TD]
[TD]2.73[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 – Need to transfer data from Sheet 1 to Sheet 2 using cross-reference table (Sheet3)
[TABLE="width: 459"]
<tbody>[TR]
[TD]Inventory #[/TD]
[TD]1/28/2012[/TD]
[TD]2/28/2012[/TD]
[TD]3/28/2012[/TD]
[TD]4/28/2012[/TD]
[TD]5/28/2012[/TD]
[TD]6/28/2012[/TD]
[/TR]
[TR]
[TD]378-065-6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]431-67-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]578-004-32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]696-0235-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]727-34-34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]886-563-3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet3 – cross-reference from Stock # to Inv #
[TABLE="width: 180"]
<tbody>[TR]
[TD]Stock #[/TD]
[TD]Inventory #[/TD]
[/TR]
[TR]
[TD]3768LK[/TD]
[TD]431-67-1[/TD]
[/TR]
[TR]
[TD]KJ 3-478[/TD]
[TD]578-004-32[/TD]
[/TR]
[TR]
[TD]M-y78[/TD]
[TD]378-065-6[/TD]
[/TR]
[TR]
[TD]tt-67[/TD]
[TD]886-563-3[/TD]
[/TR]
[TR]
[TD]9-7-HE[/TD]
[TD]727-34-34[/TD]
[/TR]
[TR]
[TD]8-PM-9[/TD]
[TD]696-0235-1[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your assistance.