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

F

fujiman

New Member
Joined
Aug 25, 2017
Messages
9
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
 
Sort by date Sort by votes
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
ABCDEFGHIJKLMNO
1
20.3796770.886280.6268240.4121710.3876890.84120.1344910.725360.6385890.9092180.9689950.0626350.8913060.303251No
30.7643310.7473820.2387580.4591410.0710180.3077380.9291930.2422140.4486510.988970.424810.6342920.9201230.011453Yes
40.9431220.9284840.4733030.3254940.1025180.3646080.339590.3728060.9459240.7843540.6544650.1139960.5893070.550181No
50.8173970.6568270.8711180.9513990.690290.6321450.29440.8093820.7739390.7765140.1480680.268970.8267580.132986Yes
60.4246950.6959410.6667960.176040.1901180.9505610.4379340.4006180.4734750.6912430.5344110.13410.8447760.293835No
70.6791730.6212060.4264640.2328590.680640.6746840.176270.8520670.5438470.1395540.0601670.061820.850840.476412No
80.7730130.626820.7382460.0469710.7712680.6939620.3817110.6610990.6931530.1524430.178080.8423210.6825690.719107Yes
90.5469430.2461910.9139310.9729620.6803850.7301810.9196930.620530.7217420.6433520.1018890.4787040.0591570.460015No
100.3748420.5290180.8743070.0875790.0612320.915610.8754540.8812910.3885430.3880850.5999210.2620330.2827120.505471Yes
Quotes


The code generated this output:

Excel 2013/2016
ABCDEFGHIJKLMNO
1
20.7643310.7473820.2387580.4591410.0710180.3077380.9291930.2422140.4486510.988970.424810.6342920.9201230.011453Yes
30.8173970.6568270.8711180.9513990.690290.6321450.29440.8093820.7739390.7765140.1480680.268970.8267580.132986Yes
40.7730130.626820.7382460.0469710.7712680.6939620.3817110.6610990.6931530.1524430.178080.8423210.6825690.719107Yes
50.3748420.5290180.8743070.0875790.0612320.915610.8754540.8812910.3885430.3880850.5999210.2620330.2827120.505471Yes
Sales Orders
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
[TABLE="width: 1162"]
<tbody>[TR]
[TD][TABLE="width: 1162"]
<tbody>[TR]
[TD]Comp. Quote[/TD]
[TD]DATE[/TD]
[TD]Manufacturer [/TD]
[TD]Man. Quote #[/TD]
[TD]Gear Type[/TD]
[TD]DP (in.)[/TD]
[TD]OAL (in.)[/TD]
[TD]Face Width (in)[/TD]
[TD]P. Angle (◦)[/TD]
[TD]H. Angle (◦)[/TD]
[TD]# Teeth[/TD]
[TD]Material[/TD]
[TD]Hardness (BHN)[/TD]
[TD] Price [/TD]
[TD]Sale[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD]1[/TD]
[TD]Double Helical[/TD]
[TD]2[/TD]
[TD]88[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]26[/TD]
[TD]4340[/TD]
[TD]325-365[/TD]
[TD] x [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD]2[/TD]
[TD]Pinion Shaft[/TD]
[TD]2[/TD]
[TD]88[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]26[/TD]
[TD]4340[/TD]
[TD]325-365[/TD]
[TD] x [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]d[/TD]
[TD]3[/TD]
[TD]Double Helical[/TD]
[TD]2[/TD]
[TD]88[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]26[/TD]
[TD]4340[/TD]
[TD]325-365[/TD]
[TD] x [/TD]
[TD] Yes [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD]4[/TD]
[TD]Double Helical[/TD]
[TD]2[/TD]
[TD]88[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]26[/TD]
[TD]4340[/TD]
[TD]325-365[/TD]
[TD] x [/TD]
[TD] Yes [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]e[/TD]
[TD]5[/TD]
[TD]Double Helical[/TD]
[TD]2[/TD]
[TD]88[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]26[/TD]
[TD]4340[/TD]
[TD]325-365[/TD]
[TD] x [/TD]
[TD] Yes [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]e[/TD]
[TD]6[/TD]
[TD]Double Helical[/TD]
[TD]2[/TD]
[TD]88[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]26[/TD]
[TD]4340[/TD]
[TD]325-365[/TD]
[TD] x [/TD]
[TD] Yes [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]a[/TD]
[TD]78[/TD]
[TD]Double Helical[/TD]
[TD]2[/TD]
[TD]88[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]26[/TD]
[TD]4340[/TD]
[TD]325-365[/TD]
[TD] x [/TD]
[TD] No [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]d[/TD]
[TD]9[/TD]
[TD]Double Helical[/TD]
[TD]2[/TD]
[TD]88[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]26[/TD]
[TD]4340[/TD]
[TD]325-365[/TD]
[TD] x [/TD]
[TD] Yes [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]b[/TD]
[TD]9[/TD]
[TD]Double Helical[/TD]
[TD]2[/TD]
[TD]88[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]26[/TD]
[TD]4340[/TD]
[TD]325-365[/TD]
[TD] x [/TD]
[TD] Yes [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]d[/TD]
[TD]0[/TD]
[TD]Double Helical[/TD]
[TD]2[/TD]
[TD]88[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]26[/TD]
[TD]4340[/TD]
[TD]325-365[/TD]
[TD] x [/TD]
[TD] Yes [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]f[/TD]
[TD]9[/TD]
[TD]Double Helical[/TD]
[TD]2[/TD]
[TD]88[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]26[/TD]
[TD]4340[/TD]
[TD]325-365[/TD]
[TD] x [/TD]
[TD] Yes [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]





[/TD]
[/TR]
</tbody>[/TABLE]
Quotes


[TABLE="width: 1065"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]a[/TD]
[TD="align: right"]1[/TD]
[TD]Double Helical[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]4340[/TD]
[TD]325-365[/TD]
[TD]x[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]b[/TD]
[TD="align: right"]2[/TD]
[TD]Pinion Shaft[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]4340[/TD]
[TD]325-365[/TD]
[TD]x[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]
Sales Orders
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
211
kbk
K
W
  • Question Question
Move Row to Another Sheet Based on Multiple Cell Values
Replies
9
Views
487
jolivanes
jolivanes
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
196
NdNoviceHlp
NdNoviceHlp
L
  • Solved
copy cell and cell above onto different sheet, under the next available line.
Replies
5
Views
187
Alex Blakenburg
A
L
  • Question Question
Selection based on two variables - copy and paste to another sheet
Replies
3
Views
363
kvsrinivasamurthy
K

Forum statistics

Threads
1,223,956
Messages
6,175,607
Members
452,660
Latest member
Zatman

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top