COPYING DATA FROM SHEET 1 BASED ON VALUE IN COLUMN P

[livetolearn4life]

Hello Im trying to move to a new sheet, specific rows and columns from sheet 1 if the value in column P meets a specific criteria ( eg tool shop). then loop for the next specific value in Column P (eg Stock Room) and move the specific rows and columns to sheet 3 data and so forth and so on.

Here is sheet 1 with all the data... below this I posted what the specific row and columns I would like to paste into the new sheet.

testing.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ORDNO	REFNO	JOBNO	FITEM	DESC	OSTAT	CRDT	ODUDT	CRUS	R REMAINING	MO AREA	DT ACCEPTED	DT REJECTED	DUE DT	TARGET	AREA	FOCAL
2	JBAA146A	REDD870	DIM8650	VFMZZ4	VFM TOOL	40	1210422	1210429	GH	GNVHT	SHOPPY			04/29/21	KENTUCKY	TOOL SHOP	ZEEKY
3	JB27A38A	DISB690	DOM6830	SAZZ2Z	SA TOOL	10	1210602	1210608	SFT	GNVHT	SHOPPY			06/08/21	ALABAMA	TOOL SHOP	ZEEKY
4	JB237A5A	MTRMLDI	MTRML	SA2176	SA TOOL	40	1210527	1210610	SFT	GNVHT	SHOPPY			06/10/21	VEGAS	TOOL SHOP	ZEEKY
5	JB27A39A	REDD190	DIM8720	ADTZZ3	DOOM TOOL	10	1210602	1210608	SFT	GNVHT	SHOPPY			06/08/21	TEXAS	TOOL SHOP	ZEEKY
6	JB94A3AA	REDC620	DIM8080	HFZZ34	HF TOOL	40	1210412	1210426	SFT	GNVHT	SHOPPY			04/26/21	KENTUCKY	TOOL SHOP	ZEEKY
7	JB26126A	REDD870	DIM8650	HFZZ4Z	HF TOOL	40	1210601	1210602	SFT	GNVHT	SHOPPY			06/02/21	KENTUCKY	TOOL SHOP	ZEEKY
8	JB26127A	REDD870	DIM8650	FMLDZZ	JETSON	10	1210601	1210602	SFT	GNVHT	SHOPPY			06/02/21	KENTUCKY	TOOL SHOP	ZEEKY
9	JB22253A	REDD870	DIM8650	SF4165	SF TOOL	10	1210526	1210526	SFT	GNVHT	SHOPPY			05/26/21	KENTUCKY	TOOL SHOP	ZEEKY
10	JB3A667A	REDD980	DIM8680	HFZZ32	HF TOOL	40	1210609	1210610	SFT	GNVHT	SHOPPY			06/10/21	FLORIDA	TOOL SHOP	ZEEKY
11	JB94269A	TRU6300	DIM6300	ADT241	CUU TOOL	10	1210413	1210415	SFT	GNVHT	SHOPPY			04/15/21	DELWARE	TOOL SHOP	ZEEKY
12	JB23715A	TRU6300	DIM6300	FDZ650	FD TOOL	10	1210527	1210603	SFT	GNVHT	SHOPPY			06/03/21	DELWARE	TOOL SHOP	ZEEKY
13	JB23714A	TRU6300	DIM6300	PBDZ65	PBD TOOL	40	1210527	1210603	SFT	GNVHT	SHOPPY			06/03/21	DELWARE	TOOL SHOP	ZEEKY
14	JB23696A	TRU6300	DIM6300	PBFDZ4	PBFD TOOL	40	1210527	1210603	SFT	GNVHT	SHOPPY			06/03/21	DELWARE	TOOL SHOP	ZEEKY
15	JB3A799A	DISC360	DOM7050	2SAZZ4	SA	10	1210609	1210616	SFT	GNVHT	SHOPPY			06/16/21	MAINE	TOOL SHOP	ZEEKY
16	JB3A723A	DISC360	DOM7050	SAZZ49	SHOP AIDE TOOL	10	1210609	1210616	SFT	GNVHT	SHOPPY			06/16/21	MAINE	TOOL SHOP	ZEEKY
17	JB3A731A	DISC360	DOM7050	SAZZ49	SHOP AIDE TOOL	10	1210609	1210616	SFT	GNVHT	SHOPPY			06/16/21	MAINE	TOOL SHOP	ZEEKY
18	JB237A4A	MTRMLDI	MTRML	SA4110	SA TOOL	40	1210527	1210610	SFT	BJU, FSPCE	JIM OR BOB?			06/10/21	VEGAS	TOOL SHOP	ZEEKY
19	JB21654A	REDD870	DIM8650	2ADTZZ	DIM TOOL	40	1210525	1210531	SFT	BJU, FSPCE	JIM OR BOB?			05/31/21	KENTUCKY	TOOL SHOP	ZEEKY
20	JB21656A	REDD870	DIM8650	3ADTZZ	DIM TOOL	40	1210525	1210531	SFT	BJU, FSPCE	JIM OR BOB?			05/31/21	KENTUCKY	TOOL SHOP	ZEEKY
21	JB21611A	REDD870	DIM8650	ADTZZ5	DOG TOOL	40	1210525	1210531	SFT	BJU, FSPCE	JIM OR BOB?			05/31/21	KENTUCKY	TOOL SHOP	ZEEKY
22	JB23635A	REDD870	DIM8650	ZZ4Z30	TIR	40	1210527	1210527	SFT	BJU, FSPCE	JIM OR BOB?			05/27/21	KENTUCKY	TOOL SHOP	ZEEKY
23	JB94A7AA	DISD430	JIM504	ZZ2904	SPACER-Z400	40	1210412	1210419	GED	NOTES	CERGY	05/24/21		04/19/21	TULSA	STOCK ROOM	GLORY
24	JBA7582A	DISD430	JIM504	ZZ6315	TURNIP GREEN	40	1210504	1210510	GED	NOTES	STOCK CLERK	06/08/21		05/10/21	TULSA	STOCK ROOM	GLORY
25	JBA76A2A	DISD430	JIM504	ZZ6315	TURNIP GREEN	40	1210504	1210505	GED	NOTES	BOBAN	06/08/21		05/05/21	TULSA	STOCK ROOM	GLORY
26	JB67426A	REDC810	JIM504	414110	REAR INNER TUBE ASSY	40	1210222	1210211	GED	NOTES	BOBAN	06/08/21		02/11/21	ALABAMA	STOCK ROOM	GLORY
27	JB95289A	REDD980	JIM504	ZZ3162	SUPPORT-JACKET	40	1210415	1210506	GED	NOTES	CERGY	05/27/21		05/06/21	FLORIDA	STOCK ROOM	GLORY
28	JB91177A	REDD980	JIM504	ZZ3274	GOBBLER	40	1210407	1210421	DBY	NOTES	BOBAN		06/07/21	04/21/21	FLORIDA	STOCK ROOM	GLORY
29	JB94278A	REDD980	JIM504	ZZ3217	GOBBLER	40	1210413	1210428	DBY	NOTES	BOBAN		06/03/21	04/28/21	FLORIDA	STOCK ROOM	GLORY
30	JB74511A	REDD730	JIM504	Z74595	JACKET	40	1210303	1210505	DBY	NOTES	BOBAN			05/05/21	KENTUCKY	STOCK ROOM	GLORY
31	JB17362A	REDD940	JIM504	ZZ2903	TURNIP GREEN	40	1210518	1210518	NDS	FSPCE	GOOBS			05/18/21	FLORIDA	QA INSPECTION	DUMMS
32	JB17365A	REDD940	JIM504	ZZ2903	TURNIP GREEN	40	1210518	1210518	NDS	FSPCE	GOOBS			05/18/21	FLORIDA	QA INSPECTION	DUMMS
33	JB1843AA	REDD940	JIM504	ZZ2903	TURNIP GREEN	40	1210519	1210527	NDS	FSPCE	GOOBS			05/27/21	FLORIDA	QA INSPECTION	DUMMS
34	JB18431A	REDD940	JIM504	ZZ2903	TURNIP GREEN	40	1210519	1210527	NDS	FSPCE	GOOBS			05/27/21	FLORIDA	QA INSPECTION	DUMMS
35	JB26117A	REDD980	JIM504	ZZ3162	SUPPORT-JACKET	40	1210601	1210525	NDS	FSPCE	GOOBS	06/09/21		05/25/21	FLORIDA	QA INSPECTION	DUMMS
36	JB21452A	TRU6300	JIM504	416510	BUNGY BALL BAR	40	1210525	1210525	NDS	FSPCE	GOOBS			05/25/21	DELWARE	QA INSPECTION	DUMMS
37	JB74653A	REDD730	JIM504	Z75922	QUADRANT-STD,Z110W	40	1210303	1210325	GED	FSPCE, HO, MSPCE, PRC, FSPCE	GOOBS			03/25/21	KENTUCKY	QA INSPECTION	DUMMS
38	JB74654A	REDD730	JIM504	Z75922	QUADRANT-STD,Z110W	40	1210303	1210325	GED	FSPCE, HO, MSPCE, PRC, FSPCE	GOOBS			03/25/21	KENTUCKY	QA INSPECTION	DUMMS
39	JB79925A	REDD100	JIM504	ZZ1242	SPREADER-OFS,F/R,Z110I,UPPER	40	1210315	1751231	GED	FSPCE, BG1, HO, VRGBP, RSPCE, FSPCE	GOOBS			12/31/75	KANSAS	QA INSPECTION	DUMMS
40	JB144A7A	REDC200	JIM504	41Z612	TUBE ASSY-FWD	40	1210513	1201130	GED	FSPCE	GOOBS			11/30/20	TEXAS	QA INSPECTION	DUMMS
41	JB13789A	DISD430	JIM504	ZZ1151	TURNIP GREEN	40	1210512	1210512	GED	FSPCE	GOOBS			05/12/21	TULSA	QA INSPECTION	DUMMS
42	JB9A914A	DISD430	JIM504	ZZ124Z	GOBBLER	40	1210406	1210603	DBY	FSPCE	GOOBS			06/03/21	TULSA	QA INSPECTION	DUMMS
43	JBA99A3A	REDC810	JIM504	413010	INSERT	40	1210506	1210506	DBY	FSPCE	GOOBS			05/06/21	ALABAMA	QA INSPECTION	DUMMS
44	JBA99A4A	REDC810	JIM504	413010	INSERT	40	1210506	1210506	DBY	FSPCE	GOOBS			05/06/21	ALABAMA	QA INSPECTION	DUMMS
45	JB21581A	REDD730	JIM504	Z69374	GOBBLER	40	1210525	1210525	DBY	FSPCE	GOOBS			05/25/21	KENTUCKY	QA INSPECTION	DUMMS
46	JBAAA43A	REDD980	JIM504	ZZ62Z6	GOBBLER	40	1210422	1210504	DBY	FSPCE	GOOBS			05/04/21	FLORIDA	QA INSPECTION	DUMMS
47	JBAAA44A	REDD980	JIM504	ZZ62Z6	GOBBLER	40	1210422	1210504	DBY	FSPCE	GOOBS			05/04/21	FLORIDA	QA INSPECTION	DUMMS
48	JBAAA47A	REDD980	JIM504	ZZ3274	GOBBLER	40	1210422	1210503	DBY	FSPCE	GOOBS			05/03/21	FLORIDA	QA INSPECTION	DUMMS
49	JBA8514A	REDD980	JIM504	ZZ4623	GOBBLER	40	1210505	1210519	DBY	FSPCE	GOOBS			05/19/21	FLORIDA	QA INSPECTION	DUMMS
50	JB982A3A	REDD980	JIM504	ZZ3272	GOBBLER	40	1210421	1210505	DBY	FSPCE	GOOBS			05/05/21	FLORIDA	QA INSPECTION	DUMMS
51	JB27A64A	REDD980	JIM504	Z77062	JACKET	40	1210603	1210429	DBY	FSPCE, HP, VRPRC, RSPCE, FSPCE	GOOBS			04/29/21	FLORIDA	QA INSPECTION	DUMMS
52	JB19466A	REDD730	JIM504	Z69374	GOBBLER	40	1210524	1210527	DBY	FSPCE	GOOBS			05/27/21	KENTUCKY	QA INSPECTION	DUMMS
53	JBA5537A	REDD870	DIM8650	ZZ4Z31	TIR	40	1210429	1751231	SFT	GNVST, GNVST, GNVHT, GNVST, SPCE	HARRY			12/31/75	KENTUCKY	FABRICATION	BILLY RAY
54	JB24589A	DISD430	DOM8670	ZZ46Z0	TIR	40	1210528	1751231	SFT	GNVHT, GNVST, SPCE	HARRY			12/31/75	TULSA	FABRICATION	BILLY RAY
55	JB2459AA	DISD430	DOM8670	ZZ46Z0	TIR	40	1210528	1751231	SFT	GNVHT, GNVST, SPCE	HARRY			12/31/75	TULSA	FABRICATION	BILLY RAY
56	JB277A8A	REDD940	DIM8670	ZZ4525	TIR	40	1210603	1210610	SFT	PI, GNVST, GNVST, SPCE	HARRY			06/10/21	FLORIDA	FABRICATION	BILLY RAY
57	JB277A9A	REDD940	DIM8670	ZZ4525	TIR	40	1210603	1210610	SFT	PI, GNVST, GNVST, SPCE	HARRY			06/10/21	FLORIDA	FABRICATION	BILLY RAY
58	JB2771AA	REDD940	DIM8670	ZZ4525	TIR	40	1210603	1210610	SFT	PI, GNVST, GNVST, SPCE	HARRY			06/10/21	FLORIDA	FABRICATION	BILLY RAY
59	JB27711A	REDD940	DIM8670	ZZ4525	TIR	40	1210603	1210610	SFT	PI, GNVST, GNVST, SPCE	HARRY			06/10/21	FLORIDA	FABRICATION	BILLY RAY
60	JB27712A	REDD940	DIM8670	ZZ4525	TIR	40	1210603	1210610	SFT	PI, GNVST, GNVST, SPCE	HARRY			06/10/21	FLORIDA	FABRICATION	BILLY RAY
61	JB27713A	REDD940	DIM8670	ZZ4525	TIR	40	1210603	1210610	SFT	PI, GNVST, GNVST, SPCE	HARRY			06/10/21	FLORIDA	FABRICATION	BILLY RAY
62	JB27686A	REDD940	DIM8670	ZZ4525	TIR	40	1210603	1210610	SFT	PI, GNVST, GNVHT, GNVST, SPCE	HARRY			06/10/21	FLORIDA	FABRICATION	BILLY RAY
63	JB27685A	TRU6300	DIM6300	Z57126	TIR	40	1210603	1210610	SFT	PI, GNVST, SPCE	HARRY			06/10/21	DELWARE	FABRICATION	BILLY RAY
64	JB27673A	DIS5750	DIM6300	Z57126	TIR	40	1210603	1210610	SFT	PI, GNVHT, GNVST, SPCE	HARRY			06/10/21	TEXAS	FABRICATION	BILLY RAY
65	JB27654A	REDD870	DIM8650	ZZ6154	TIR	40	1210603	1210610	SFT	PI, GNVHT, GNVST, SPCE	HARRY			06/10/21	KENTUCKY	FABRICATION	BILLY RAY
66	JB26893A	TRU6300	DIM6300	Z6Z32Z	TIR	40	1210602	1210602	SFT	PI, GNVHT, GNVST, SPCE	HARRY			06/02/21	DELWARE	FABRICATION	BILLY RAY
67	JB26894A	TRU6300	DIM6300	Z5Z637	TIR	40	1210602	1210608	SFT	PI, GNVHT, GNVST, SPCE	HARRY			06/08/21	DELWARE	FABRICATION	BILLY RAY
68	JB27653A	TRU6300	DIM6300	Z62Z07	TIR	40	1210603	1210610	SFT	PI, GNVHT, GNVST, SPCE	HARRY			06/10/21	DELWARE	FABRICATION	BILLY RAY
69	JB27668A	TRU6300	DIM6300	Z57126	TIR	40	1210603	1210610	SFT	PI, GNVHT, GNVST, SPCE	HARRY			06/10/21	DELWARE	FABRICATION	BILLY RAY
70	JB3A666A	RED9740	DIM6160	ZZ3166	TIR	10	1210609	1210616	SFT	GPS, PI, PVF, DMS, SPCE	HARRY			06/16/21	KANSAS	FABRICATION	BILLY RAY
71	JB293A6A	REDC620	JIM504	ZZ347Z	GOBBLER	40	1210607	1210608	DBY	PI, PVF, PLM, DMS, PLM, FSPCE	HARRY			06/08/21	KENTUCKY	FABRICATION	BILLY RAY
72	JB272A9A	REDD020	JIM504	ZZ3274	GOBBLER	40	1210603	1210420	DBY	PI, PVF, DMS, MD1, FSPCE	HARRY			04/20/21	KANSAS	FABRICATION	BILLY RAY
73	JB27211A	REDD020	JIM504	ZZ3274	GOBBLER	40	1210603	1210421	DBY	PI, PVF, DMS, MD1, FSPCE	HARRY			04/21/21	KANSAS	FABRICATION	BILLY RAY
74	JB27A23A	REDD100	JIM504	ZZ59Z3	GOBBLER	40	1210602	1210520	DBY	PI, PVF, DMS, FSPCE	HARRY			05/20/21	KANSAS	FABRICATION	BILLY RAY
75	JB27212A	REDC810	JIM504	413010	INSERT	40	1210603	1210421	DBY	PI, PLU, PLM, FSPCE	HARRY			04/21/21	ALABAMA	FABRICATION	BILLY RAY
76	JB3A8A4A	REDC980	JIM504	ZZ1249	GOBBLER	10	1210609	1210610	DBY	GPS, PI, PVF, DMS, PLM, FSPCE	HARRY			06/10/21	ATLANTA	FABRICATION	BILLY RAY
77	JB3A8A5A	REDC980	JIM504	ZZ1249	GOBBLER	10	1210609	1210610	DBY	GPS, PI, PVF, DMS, PLM, FSPCE	HARRY			06/10/21	ATLANTA	FABRICATION	BILLY RAY
78	JB3A8A6A	REDC980	JIM504	ZZ1249	GOBBLER	10	1210609	1210610	DBY	GPS, PI, PVF, DMS, PLM, FSPCE	HARRY			06/10/21	ATLANTA	FABRICATION	BILLY RAY
79	JB3A8A7A	REDC980	JIM504	ZZ1249	GOBBLER	10	1210609	1210610	DBY	GPS, PI, PVF, DMS, PLM, FSPCE	HARRY			06/10/21	ALABAMA	FABRICATION	BILLY RAY
80	JB2721AA	REDD980	JIM504	ZZ3274	GOBBLER	10	1210603	1210421	DBY	GPS, PI, PVF, DMS, MD1, FSPCE	HARRY			04/21/21	FLORIDA	FABRICATION	BILLY RAY
81	JB3A639A	REDD980	JIM504	ZZ3274	GOBBLER	10	1210609	1210421	DBY	GPS, PI, PVF, DMS, MD1, FSPCE	HARRY			04/21/21	FLORIDA	FABRICATION	BILLY RAY
82	JB2A6A7A	REDD100	JIM504	ZZ5967	GOBBLER	10	1210524	1210524	DBY	GPS, PI, PVF, DMS, FSPCE	HARRY			05/24/21	KANSAS	FABRICATION	BILLY RAY
83	JB26525A	REDD870	JIM504	ZZ6156	TURNIP GREEN	10	1210602	1210607	NDS	GPS, BJU, FSPCE	JIMBO			06/07/21	KENTUCKY	FABRICATION	JIMMY BOOB
84	JB2696AA	REDD870	JIM504	ZZ6156	TURNIP GREEN	10	1210602	1210608	NDS	GPS, BJU, FSPCE	JIMBO			06/08/21	KENTUCKY	FABRICATION	JIMMY BOOB
85	JB26961A	REDD870	JIM504	ZZ6156	TURNIP GREEN	10	1210602	1210608	NDS	GPS, BJU, FSPCE	JIMBO			06/08/21	KENTUCKY	FABRICATION	JIMMY BOOB
86	JB26949A	REDD100	JIM504	ZZ6105	TURNIP GREEN	10	1210602	1210408	NDS	GPS, BJU, FSPCE	JIMBO			04/08/21	KANSAS	FABRICATION	JIMMY BOOB
87	JB2695AA	REDD100	JIM504	ZZ6105	TURNIP GREEN	10	1210602	1210408	NDS	GPS, BJU, FSPCE	JIMBO			04/08/21	KANSAS	FABRICATION	JIMMY BOOB
88	JB26524A	REDD870	JIM504	ZZ6156	TURNIP GREEN	40	1210602	1210607	NDS	BJU, FSPCE	JIMBO			06/07/21	KENTUCKY	FABRICATION	JIMMY BOOB
89	JB52A13A	REDD100	JIM504	ZZ5422	LEG ASSY-AISLE,NAR,Z110I	40	1210120	1210127	NDS	GIP, SPA, LCA, FSPCE	JIM OR BOB?			01/27/21	KANSAS	FABRICATION	JANEY
90	JB2822AA	REDD730	JIM504	ZZ6302	WISH BONES	10	1210607	1210607	DBY	GIP, FA4, FSPCE	TRACKY			06/07/21	KENTUCKY	FABRICATION	JANEY
91	JB28221A	REDD730	JIM504	ZZ6302	WISH BONES	10	1210607	1210607	DBY	GIP, FA4, FSPCE	TRACKY			06/07/21	KENTUCKY	FABRICATION	JANEY
92	JB16947A	REDD870	JIM504	ZZ6302	WISH BONES	10	1210518	1210517	DBY	GIP, FA4, FSPCE	TRACKY			05/17/21	KENTUCKY	FABRICATION	JANEY
93	JB2766AA	REDD980	JIM504	Z77062	JACKET	40	1210603	1210429	NDS	MBM, ST2, DB1, FSPCE, HP, VRHO, AGE, RSPCE, VRPRC, RSPCE, FSPCE	JIM OR BOB?			04/29/21	FLORIDA	FABRICATION	JANEY
94	JB3A812A	TRU8300	DIM8300	ZZ07Z6	TIR	10	1210609	1210610	SFT	GPS, PLU, SPCE	GOOBS			06/10/21	TEXAS	FABRICATION	BOBBS
95	JB89723A	DISD430	DOM8670	ZZ1209	BUNGY BALL	10	1210401	1210408	SFT	GPS, GG2, SPCE	BOBAN			04/08/21	TULSA	FABRICATION	TERRAN
96	JB27674A	REDD870	DIM8650	ZZ547Z	TIR	10	1210603	1210608	SFT	GPS, GG2, SPCE	BOBAN			06/08/21	KENTUCKY	FABRICATION	TERRAN
97	JB3A698A	REDD870	DIM8650	ZZ4475	TIR	10	1210609	1210623	SFT	GPS, GG2, SPCE	BOBAN			06/23/21	KENTUCKY	FABRICATION	TERRAN
98	JB24595A	TRU6300	DIM6300	ZZ2655	TIR	10	1210528	1210414	SFT	GPS, GG2, SPCE	BOBAN			04/14/21	DELWARE	FABRICATION	TERRAN
99	JB21388A	TRU6300	DIM6300	416510	TIR CF2	10	1210525	1210331	SFT	GPS, GG2, GG2, SPCE, GG2	BOBAN			03/31/21	DELWARE	FABRICATION	TERRAN
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M:M	Cell Value	between 36897 and 54808	text	NO
L:L	Cell Value	between 36911 and 54808	text	NO

this is the data I want to move to sheet 2

testing.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	DUE DT	ORDNO	REFNO	FITEM	DESC	R REMAINING	AREA	FOCAL	DT ACCEPTED	DT REJECTED	TARGET
2	04/29/21	JBAA146A	REDD870	VFMZZ4	VFM TOOL	GNVHT	TOOL SHOP	ZEEKY			KENTUCKY
3	06/08/21	JB27A38A	DISB690	SAZZ2Z	SA TOOL	GNVHT	TOOL SHOP	ZEEKY			ALABAMA
4	06/10/21	JB237A5A	MTRMLDI	SA2176	SA TOOL	GNVHT	TOOL SHOP	ZEEKY			VEGAS
5	06/08/21	JB27A39A	REDD190	ADTZZ3	DOOM TOOL	GNVHT	TOOL SHOP	ZEEKY			TEXAS
6	04/26/21	JB94A3AA	REDC620	HFZZ34	HF TOOL	GNVHT	TOOL SHOP	ZEEKY			KENTUCKY
7	06/02/21	JB26126A	REDD870	HFZZ4Z	HF TOOL	GNVHT	TOOL SHOP	ZEEKY			KENTUCKY
8	06/02/21	JB26127A	REDD870	FMLDZZ	JETSON	GNVHT	TOOL SHOP	ZEEKY			KENTUCKY
9	05/26/21	JB22253A	REDD870	SF4165	SF TOOL	GNVHT	TOOL SHOP	ZEEKY			KENTUCKY
10	06/10/21	JB3A667A	REDD980	HFZZ32	HF TOOL	GNVHT	TOOL SHOP	ZEEKY			FLORIDA
11	04/15/21	JB94269A	TRU6300	ADT241	CUU TOOL	GNVHT	TOOL SHOP	ZEEKY			DELWARE
12	06/03/21	JB23715A	TRU6300	FDZ650	FD TOOL	GNVHT	TOOL SHOP	ZEEKY			DELWARE
13	06/03/21	JB23714A	TRU6300	PBDZ65	PBD TOOL	GNVHT	TOOL SHOP	ZEEKY			DELWARE
14	06/03/21	JB23696A	TRU6300	PBFDZ4	PBFD TOOL	GNVHT	TOOL SHOP	ZEEKY			DELWARE
15	06/16/21	JB3A799A	DISC360	2SAZZ4	SA	GNVHT	TOOL SHOP	ZEEKY			MAINE
16	06/16/21	JB3A723A	DISC360	SAZZ49	SHOP AIDE TOOL	GNVHT	TOOL SHOP	ZEEKY			MAINE
17	06/16/21	JB3A731A	DISC360	SAZZ49	SHOP AIDE TOOL	GNVHT	TOOL SHOP	ZEEKY			MAINE
18	06/10/21	JB237A4A	MTRMLDI	SA4110	SA TOOL	BJU, FSPCE	TOOL SHOP	ZEEKY			VEGAS
19	05/31/21	JB21654A	REDD870	2ADTZZ	DIM TOOL	BJU, FSPCE	TOOL SHOP	ZEEKY			KENTUCKY
20	05/31/21	JB21656A	REDD870	3ADTZZ	DIM TOOL	BJU, FSPCE	TOOL SHOP	ZEEKY			KENTUCKY
21	05/31/21	JB21611A	REDD870	ADTZZ5	DOG TOOL	BJU, FSPCE	TOOL SHOP	ZEEKY			KENTUCKY
22	05/27/21	JB23635A	REDD870	ZZ4Z30	TIR	BJU, FSPCE	TOOL SHOP	ZEEKY			KENTUCKY
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J:J	Cell Value	between 36897 and 54808	text	NO
I:I	Cell Value	between 36911 and 54808	text	NO

and for every unique value in column p keep putting the data in new sheets

 

[Saurabhj]

Hi,

I used a button and on click of button used below VBA to move data from one sheet to another. Sheet4 is the target sheet.

Please check if this solves the problem.

Private Sub cmdMoveData_Click()
Dim lastRow As Integer, rowno As Integer
Dim destRowNo As Integer
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
destRowNo = 2
For rowno = 2 To lastRow
    If ActiveSheet.Range("P" & rowno) = "TOOL SHOP" Then
        Sheets("Sheet4").Cells(destRowNo, 1) = ActiveSheet.Range("N" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 2) = ActiveSheet.Range("A" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 3) = ActiveSheet.Range("B" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 4) = ActiveSheet.Range("D" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 5) = ActiveSheet.Range("E" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 6) = ActiveSheet.Range("J" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 7) = ActiveSheet.Range("P" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 8) = ActiveSheet.Range("Q" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 9) = ActiveSheet.Range("L" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 10) = ActiveSheet.Range("M" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 11) = ActiveSheet.Range("O" & rowno)
        destRowNo = destRowNo + 1
    End If
Next
Application.ScreenUpdating = True
End Sub

 

[vcoolio]

Hello LTL4L,

My understanding of your query is that you have one "Master" sheet and four destination sheets(named "TOOL SHOP", "STOCK ROOM", "QA INSPECTION" AND "FABRICATION").
In Column P, you have criteria named the same as the destination worksheets and you'd like a VBA code to iterate through the criteria and transfer the relevant rows of data to their respective worksheets. If I've assumed correctly, then this code should do the task for you:-

Option Explicit
Sub Test()

        Dim sh As Worksheet, wsD As Worksheet, x As Long, nrow As Long
        Dim ar As Variant, ClArr As Variant, pArr As Variant, i As Long
        
        ar = Array("TOOL SHOP", "STOCK ROOM", "QA INSPECTION", "FABRICATION")
        ClArr = Array(14, 1, 2, 4, 5, 10, 16, 17, 15)
        pArr = Array("A", "B", "C", "D", "E", "F", "G", "H", "K")
        Set sh = Sheet1

Application.ScreenUpdating = False
        
        For i = 0 To UBound(ar)
                        Set wsD = Sheets(ar(i))
                        wsD.UsedRange.Offset(1).Clear
                        nrow = wsD.Cells(Rows.Count, 1).End(xlUp).Row + 1
                With sh.[A1].CurrentRegion
                        .AutoFilter 16, ar(i)
                                With .Offset(1)
                                        For x = LBound(ClArr) To UBound(ClArr)
                                              .Columns(ClArr(x)).Copy wsD.Range(pArr(x) & nrow)
                                        Next x
                                End With
                        .AutoFilter
                        wsD.Columns.AutoFit
                End With
        Next i
        
MsgBox "All done", vbExclamation
Application.ScreenUpdating = True

End Sub

I've created a sample workbook based on your supplied information which you'll find here.
Click on the "TEST" button to see how it works.

I hope that this helps.

Cheerio,
vcoolio.

 

[livetolearn4life]

Hi,

I used a button and on click of button used below VBA to move data from one sheet to another. Sheet4 is the target sheet.

Please check if this solves the problem.

Private Sub cmdMoveData_Click()
Dim lastRow As Integer, rowno As Integer
Dim destRowNo As Integer
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
destRowNo = 2
For rowno = 2 To lastRow
    If ActiveSheet.Range("P" & rowno) = "TOOL SHOP" Then
        Sheets("Sheet4").Cells(destRowNo, 1) = ActiveSheet.Range("N" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 2) = ActiveSheet.Range("A" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 3) = ActiveSheet.Range("B" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 4) = ActiveSheet.Range("D" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 5) = ActiveSheet.Range("E" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 6) = ActiveSheet.Range("J" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 7) = ActiveSheet.Range("P" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 8) = ActiveSheet.Range("Q" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 9) = ActiveSheet.Range("L" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 10) = ActiveSheet.Range("M" & rowno)
        Sheets("Sheet4").Cells(destRowNo, 11) = ActiveSheet.Range("O" & rowno)
        destRowNo = destRowNo + 1
    End If
Next
Application.ScreenUpdating = True
End Sub

Hi Saurabhj,
thanks for the work making this code. It does do what i was asking for in my post. i just need for the code to keep looping for every unique value in P and put them on separate sheets and there was another post where someone read my mind, lol.. it is an array and i will go with that one. again thanks so much!

 

[livetolearn4life]

Hello LTL4L,

My understanding of your query is that you have one "Master" sheet and four destination sheets(named "TOOL SHOP", "STOCK ROOM", "QA INSPECTION" AND "FABRICATION").
In Column P, you have criteria named the same as the destination worksheets and you'd like a VBA code to iterate through the criteria and transfer the relevant rows of data to their respective worksheets. If I've assumed correctly, then this code should do the task for you:-

Option Explicit
Sub Test()

        Dim sh As Worksheet, wsD As Worksheet, x As Long, nrow As Long
        Dim ar As Variant, ClArr As Variant, pArr As Variant, i As Long
       
        ar = Array("TOOL SHOP", "STOCK ROOM", "QA INSPECTION", "FABRICATION")
        ClArr = Array(14, 1, 2, 4, 5, 10, 16, 17, 15)
        pArr = Array("A", "B", "C", "D", "E", "F", "G", "H", "K")
        Set sh = Sheet1

Application.ScreenUpdating = False
       
        For i = 0 To UBound(ar)
                        Set wsD = Sheets(ar(i))
                        wsD.UsedRange.Offset(1).Clear
                        nrow = wsD.Cells(Rows.Count, 1).End(xlUp).Row + 1
                With sh.[A1].CurrentRegion
                        .AutoFilter 16, ar(i)
                                With .Offset(1)
                                        For x = LBound(ClArr) To UBound(ClArr)
                                              .Columns(ClArr(x)).Copy wsD.Range(pArr(x) & nrow)
                                        Next x
                                End With
                        .AutoFilter
                        wsD.Columns.AutoFit
                End With
        Next i
       
MsgBox "All done", vbExclamation
Application.ScreenUpdating = True

End Sub

I've created a sample workbook based on your supplied information which you'll find here.
Click on the "TEST" button to see how it works.

I hope that this helps.

Cheerio,
vcoolio.

Hello LTL4L,

My understanding of your query is that you have one "Master" sheet and four destination sheets(named "TOOL SHOP", "STOCK ROOM", "QA INSPECTION" AND "FABRICATION").
In Column P, you have criteria named the same as the destination worksheets and you'd like a VBA code to iterate through the criteria and transfer the relevant rows of data to their respective worksheets. If I've assumed correctly, then this code should do the task for you:-

Option Explicit
Sub Test()

        Dim sh As Worksheet, wsD As Worksheet, x As Long, nrow As Long
        Dim ar As Variant, ClArr As Variant, pArr As Variant, i As Long
       
        ar = Array("TOOL SHOP", "STOCK ROOM", "QA INSPECTION", "FABRICATION")
        ClArr = Array(14, 1, 2, 4, 5, 10, 16, 17, 15)
        pArr = Array("A", "B", "C", "D", "E", "F", "G", "H", "K")
        Set sh = Sheet1

Application.ScreenUpdating = False
       
        For i = 0 To UBound(ar)
                        Set wsD = Sheets(ar(i))
                        wsD.UsedRange.Offset(1).Clear
                        nrow = wsD.Cells(Rows.Count, 1).End(xlUp).Row + 1
                With sh.[A1].CurrentRegion
                        .AutoFilter 16, ar(i)
                                With .Offset(1)
                                        For x = LBound(ClArr) To UBound(ClArr)
                                              .Columns(ClArr(x)).Copy wsD.Range(pArr(x) & nrow)
                                        Next x
                                End With
                        .AutoFilter
                        wsD.Columns.AutoFit
                End With
        Next i
       
MsgBox "All done", vbExclamation
Application.ScreenUpdating = True

End Sub

I've created a sample workbook based on your supplied information which you'll find here.
Click on the "TEST" button to see how it works.

I hope that this helps.

Cheerio,
vcoolio.

Hello Vcoolio,

sorry i didn't explain it well in my post but you assumed 100% correct. The Code works great! thanks for your work it is greatly appreciated!

p.s. i got to learn how to do this array stuff.

 

[vcoolio]

You're welcome LTL4L. I'm glad to have been able to assist and thanks for the feed back.

Cheerio,
vcoolio.