Looking Up Values on One Workbook and Inserting into Another

[FrenchCelt]

Hello,

I'm trying to code a macro that will cross reference a value between two workbooks to return another value. For example, on the originating workbook:

Shipment ID	Store	Cut Time	Destination City	ST	Order ID	CP/XD Indicator	LBS	Cube	Lines	Load	Carrier	Ship Lane	Wave
2221781	1777	TU9AM	EAST TAWAS	MI	84380436		30503	2147	1096		WVAS
2221781	1777	TU9AM	EAST TAWAS	MI	84380438		3122	219	2		WVAS
2221781	1777	TU9AM	EAST TAWAS	MI	84366929	Central Pick	164	10	81		WVAS
2221781 Total							33789	2376
2221782	646	TU10AM	WEST BRANCH	MI	84381117		37986	2136	1485		WVAS
2221782	646	TU10AM	WEST BRANCH	MI	84367330	Central Pick	193	17	111		WVAS
2221782 Total							38180	2154
2221791	1295	TU7PM	BEAVER FALLS	PA	11403555	Cross Dock	300	28	1		USXI
2221791	1295	TU7PM	BEAVER FALLS	PA	84367329	Central Pick	194	16	105		USXI
2221791	1295	TU7PM	BEAVER FALLS	PA	11403591	Cross Dock	1000	49	1		USXI
2221791	1295	TU7PM	BEAVER FALLS	PA	84380443		40211	2138	1197		USXI
2221791 Total							41704	2231

I want to look up the Ship Lane and Wave from another workbook:

HOME DOOR	232	231	230	229	228	227	226	225	224	223	222	221	220	219	218	217	216	215	214	213	212	211	WAVE 1
RP 6 SH #	2221814		2221794		2221816		2221804		2221809				2221815		2221797		2221788		2221785		2221799		USXI
PICK WAVE #	WVAS.						USXI.		WVAS.				RVTS.		WVAS.		USXI.		WVAS.		WVAS.		RVTS
O#																							WVAS
E# 202106050016	4STOP				3STOP				4STOP				4STOP								4STOP		STOP 2
LINES / GRABS																							STOP 3
ODD-	131		633		1994		642		570				635		570		2448		131		639		STOP 4
EVEN- 6717 / 12259																							Total
			XD		XD		XD								XD		XD		XD
WEIGHT	4621		28859		19836		34460		9652				18369		34732		21859		32308		5134		ODD
CUBE	288		2229		1098		2172		1093				1197		2233		2196		2180		574		EVEN
LINES	4		1429		781		1086		14				53		1309		870		1163		8		Total
CUT TIME	TU11PM	0	TU10PM	0	TU9PM	0	TU2359	0	TU10PM	0	0	0	TU11PM	0	TU9PM	0	TU10PM	0	TU8PM	0	TU10PM	0
HOME DOOR	232	231	230	229	228	227	226	225	224	223	222	221	220	219	218	217	216	215	214	213	212	211	WAVE 2
RP 6 SH #	2221814	2221800	2221798	2221817	2221816	2221811	2221787		2221809	2221781	2221790	2221784	2221815	2221813	2221780	2221782	2221779	2221810	2221808	2221803	2221799	2221812	USXI
PICK WAVE #	WVAS					WVAS.			WVAS	WVAS.	WVAS.		RVTS			WVAS.	WVAS.			USXI.	WVAS	USXI.	RVTS
O#																							WVAS
E#		4STOP				3STOP												3STOP				4STOP	STOP 2
LINES / GRABS																							STOP 3
ODD-	640	584	351	1105	2067	646	2263		2448	1777	639	2375	1800	2067	519	646	2267	670	273	1207	2432	441	STOP 4
EVEN-	3STOP				2STOP				3STOP				3STOP								3STOP		Total
	XD		XD	XD			XD			XD	XD	XD	XD	XD	XD	XD	XD		XD	XD
WEIGHT	16312	9261	29004	38467	4550	8891	38000		3818	33789	34572	29530	7645	37995	31646	38180	32084	11255	40371	41974	7952	9763	ODD
CUBE	1275	624	2347	2301	449	761	2333		213	2376	2199	2311	442	2151	2159	2154	2157	397	2241	2125	261	603	EVEN
LINES	14	9	716	938	6	12	962		4	1098	1214	749	5	818	1069	1485	1370	8	1378	1549	5	34	Total
CUT TIME	TU11PM	TU2359	TU8PM	TU2359	TU9PM	TU9AM	TU9PM	0	TU10PM	TU9AM	TU8PM	TU9PM	TU11PM	TU9PM	TU11PM	TU10AM	TU9AM	TU11PM	TU11PM	TU8AM	TU10PM	TU8AM

The end result should look like this:

Shipment ID	Store	Cut Time	Destination City	ST	Order ID	CP/XD Indicator	LBS	Cube	Lines	Load	Carrier	Ship Lane	Wave
2221781	1777	TU9AM	EAST TAWAS	MI	84380436		30503	2147	1096		WVAS
2221781	1777	TU9AM	EAST TAWAS	MI	84380438		3122	219	2		WVAS
2221781	1777	TU9AM	EAST TAWAS	MI	84366929	Central Pick	164	10	81		WVAS
2221781 Total							33789	2376				223	2
2221782	646	TU10AM	WEST BRANCH	MI	84381117		37986	2136	1485		WVAS
2221782	646	TU10AM	WEST BRANCH	MI	84367330	Central Pick	193	17	111		WVAS
2221782 Total							38180	2154				217	2
2221791	1295	TU7PM	BEAVER FALLS	PA	11403555	Cross Dock	300	28	1		USXI
2221791	1295	TU7PM	BEAVER FALLS	PA	84367329	Central Pick	194	16	105		USXI
2221791	1295	TU7PM	BEAVER FALLS	PA	11403591	Cross Dock	1000	49	1		USXI
2221791	1295	TU7PM	BEAVER FALLS	PA	84380443		40211	2138	1197		USXI
2221791 Total							41704	2231				227	4

The Ship Lane and Wave # can be found in the row of HOME DOOR numbers:

HOME DOOR

232

231

230

229

228

227

226

225

224

223

222

221

220

219

218

217

216

215

214

213

212

211

WAVE 1

Further complicating this task is the presence of what we call double and triple loads. Here is an example of a triple with the Ship Lane and Wave already assigned:

2221814	131	TU8PM	HOUGHTON LAKE	MI	84380676		4621	288	4	Nose	WVAS		1
2221814	640	TU9PM	MOUNT PLEASANT	MI	11403581	Cross Dock	2100	61	1	Middle	WVAS		2
2221814	640	TU9PM	MOUNT PLEASANT	MI	84380452		13060	1107	14	Middle	WVAS
2221814	640	TU9PM	MOUNT PLEASANT	MI	11403577	Cross Dock	300	28	1	Middle	WVAS
2221814	640	TU9PM	MOUNT PLEASANT	MI	11403590	Cross Dock	300	28	1	Middle	WVAS
2221814	640	TU9PM	MOUNT PLEASANT	MI	84367510	Central Pick	251	23	96	Middle	WVAS
2221814	640	TU9PM	MOUNT PLEASANT	MI	11403582	Cross Dock	300	28	1	Middle	WVAS
2221814	273	TU11PM	GREENVILLE	MI	11403580	Cross Dock	2100	61	1	Tail	WVAS		3
2221814	273	TU11PM	GREENVILLE	MI	84381114		8599	607	7	Tail	WVAS
2221814 Total							31631	2231				232

Each wave number goes to the first instance of the Nose, Middle, and Tail values because the triple load is assigned to three different waves.

I've asked for help with complex macros before, but this one seems beyond the pale, so I wouldn't blame anyone if they just bypassed it.