Macro to copy data from one sheet and paste to another based on cell contents

[fujiman]

I have two sheets set up, they are named "Quotes" and "Sales Orders" and the information I want to start copying from the "Quotes" and pasting to the "Sales Orders". The data starts in the range of A3 - O3. I would like to have the sheet automatically copy that entire range if the contents of the O cells reads "Yes" while also maintaining its current formatting after its pastes to the "Sales Orders" sheet. Is this a possible function and can it scan every row in the sheet to copy all of the rows that meet the criteria? And have I provided enough information?
Thank you

 

[MrKowz]

Can you please provide some sample data? This is some pretty standard code, so I suspect there is something we're missing.

Using the below input:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	0.379677	0.88628	0.626824	0.412171	0.387689	0.8412	0.134491	0.72536	0.638589	0.909218	0.968995	0.062635	0.891306	0.303251	No
3	0.764331	0.747382	0.238758	0.459141	0.071018	0.307738	0.929193	0.242214	0.448651	0.98897	0.42481	0.634292	0.920123	0.011453	Yes
4	0.943122	0.928484	0.473303	0.325494	0.102518	0.364608	0.33959	0.372806	0.945924	0.784354	0.654465	0.113996	0.589307	0.550181	No
5	0.817397	0.656827	0.871118	0.951399	0.69029	0.632145	0.2944	0.809382	0.773939	0.776514	0.148068	0.26897	0.826758	0.132986	Yes
6	0.424695	0.695941	0.666796	0.17604	0.190118	0.950561	0.437934	0.400618	0.473475	0.691243	0.534411	0.1341	0.844776	0.293835	No
7	0.679173	0.621206	0.426464	0.232859	0.68064	0.674684	0.17627	0.852067	0.543847	0.139554	0.060167	0.06182	0.85084	0.476412	No
8	0.773013	0.62682	0.738246	0.046971	0.771268	0.693962	0.381711	0.661099	0.693153	0.152443	0.17808	0.842321	0.682569	0.719107	Yes
9	0.546943	0.246191	0.913931	0.972962	0.680385	0.730181	0.919693	0.62053	0.721742	0.643352	0.101889	0.478704	0.059157	0.460015	No
10	0.374842	0.529018	0.874307	0.087579	0.061232	0.91561	0.875454	0.881291	0.388543	0.388085	0.599921	0.262033	0.282712	0.505471	Yes
Quotes

The code generated this output:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	0.764331	0.747382	0.238758	0.459141	0.071018	0.307738	0.929193	0.242214	0.448651	0.98897	0.42481	0.634292	0.920123	0.011453	Yes
3	0.817397	0.656827	0.871118	0.951399	0.69029	0.632145	0.2944	0.809382	0.773939	0.776514	0.148068	0.26897	0.826758	0.132986	Yes
4	0.773013	0.62682	0.738246	0.046971	0.771268	0.693962	0.381711	0.661099	0.693153	0.152443	0.17808	0.842321	0.682569	0.719107	Yes
5	0.374842	0.529018	0.874307	0.087579	0.061232	0.91561	0.875454	0.881291	0.388543	0.388085	0.599921	0.262033	0.282712	0.505471	Yes
Sales Orders

 

[fujiman]

Here is a copy and paste of the two sheets after I apply the macro. The first is Quotes and the second is Sales orders

Comp. Quote DATE Manufacturer Man. Quote # Gear Type DP (in.) OAL (in.) Face Width (in) P. Angle (◦) H. Angle (◦) # Teeth Material Hardness (BHN) Price Sale a 1 Double Helical 2 88 25 20 30 26 4340 325-365 x Yes b 2 Pinion Shaft 2 88 25 20 30 26 4340 325-365 x Yes d 3 Double Helical 2 88 25 20 30 26 4340 325-365 x Yes a 4 Double Helical 2 88 25 20 30 26 4340 325-365 x Yes e 5 Double Helical 2 88 25 20 30 26 4340 325-365 x Yes e 6 Double Helical 2 88 25 20 30 26 4340 325-365 x Yes a 78 Double Helical 2 88 25 20 30 26 4340 325-365 x No d 9 Double Helical 2 88 25 20 30 26 4340 325-365 x Yes b 9 Double Helical 2 88 25 20 30 26 4340 325-365 x Yes d 0 Double Helical 2 88 25 20 30 26 4340 325-365 x Yes f 9 Double Helical 2 88 25 20 30 26 4340 325-365 x Yes <tbody> </tbody>

<tbody>
</tbody>

Quotes

		a	1	Double Helical	2	88	25	20	30	26	4340	325-365	x	Yes
		b	2	Pinion Shaft	2	88	25	20	30	26	4340	325-365	x	Yes

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Sales Orders

 

[MrKowz]

This is so strange.... I used your input data and the output was what the desired result is (10 rows). Let me know if you are able to email your workbook, I'll PM you my email address and try to take a look at it this evening.

 

[fujiman]

Could the problem have something to do with the line of code "rowx = 2" and " rowx = rowx +1" ? I do not know if the code continues to add onto itself or if it stops at 3 because 2+1=3?

 

[MrKowz]

You're going to think I'm crazy, but play along here... because this is honestly the first time I've ever seen this.

Take a look at the formatting in column O. In O3:O4, you have the "General" format applied. From O5 to the end, it was "Accounting" formatted. For some reason, and I'm going to have to phone-a-friend on this one, the number format of the cell affected the ability for the find method to actually find the value. I'm going to have some fun testing tonight.

Highlight column O, make sure the format is "General", and then run the macro. It should function as intended.

Cheers!

 

[fujiman]

The fix worked! Thank you so much for all of your help! You've been a real champ