Copy a range of cells to another worksheet if criteria is met

jfutrell73

New Member
Joined
Aug 9, 2013
Messages
14
Looking for help to copy and paste each cell below when M is true or greater then 0 to a new worksheet called Price Summary. If cell is false or 0, do not copy. I would like the text in N to paste to the first available row in A and copy M as well to the corresponding B. I only need the values of these cells to copy and not the formulas.
Thank you in advance for any help I can get. Been trying to get this to work for a while with no luck.


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;">$0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;">$0</td><td style=";"></td></tr></tbody></table><p style="width:14.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">250# Vessel Pricing TEST</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M17</th><td style="text-align:left">=IF(<font color="Blue">L17=TRUE,INDEX(<font color="Red">_250lb12inlegs,B2</font>),"$0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N17</th><td style="text-align:left">=IF(<font color="Blue">L17,"12 in. Legs",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M18</th><td style="text-align:left">=IF(<font color="Blue">L18>0,INDEX(<font color="Red">_250lbAdditional_Leg_Length,B2</font>)*B18,"$0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N18</th><td style="text-align:left">=IF(<font color="Blue">L18>0,(<font color="Red">"Additional "&" "&L18&"' Leg Length"</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M20</th><td style="text-align:left">=IF(<font color="Blue">L20=TRUE,INDEX(<font color="Red">_250lbStand_Pipe,B2</font>)*C20,"$0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N20</th><td style="text-align:left">=IF(<font color="Blue">L20,"12 in. Pipe Stand",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M21</th><td style="text-align:left">=IF(<font color="Blue">L21=TRUE,INDEX(<font color="Red">_250lbFlush_Saddles,B2</font>)*C21,"$0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N21</th><td style="text-align:left">=IF(<font color="Blue">L21,"Flush Saddles",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M22</th><td style="text-align:left">=IF(<font color="Blue">L22=TRUE,INDEX(<font color="Red">_250lb10in_Saddles,B2</font>)*C22,"$0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N22</th><td style="text-align:left">=IF(<font color="Blue">L22,"10 in. Saddles",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M23</th><td style="text-align:left">=IF(<font color="Blue">L23=TRUE,INDEX(<font color="Red">_250lbPerf_Plates,B2</font>)*C23,"$0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N23</th><td style="text-align:left">=IF(<font color="Blue">L23,"Perf. Plate",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M24</th><td style="text-align:left">=IF(<font color="Blue">L24=TRUE,INDEX(<font color="Red">_250lbSquare_Plates,B2</font>)*C24,"$0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N24</th><td style="text-align:left">=IF(<font color="Blue">L24,"Square Plate",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M25</th><td style="text-align:left">=IF(<font color="Blue">L25=TRUE,INDEX(<font color="Red">_250lbRound_Plates,B2</font>)*C25,"$0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N25</th><td style="text-align:left">=IF(<font color="Blue">L25,"Round Plate",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M26</th><td style="text-align:left">=IF(<font color="Blue">L26=TRUE,INDEX(<font color="Red">_250lbDemister_Pad,B2</font>)*C26,"$0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N26</th><td style="text-align:left">=IF(<font color="Blue">L26,"Demister Pad",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M27</th><td style="text-align:left">=INDEX(<font color="Blue">_Vortex_Prices,B27</font>)*C27</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N27</th><td style="text-align:left">=IF(<font color="Blue">M27>0,(<font color="Red">L27 &" "&"Vortex Eliminator"</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M28</th><td style="text-align:left">=INDEX(<font color="Blue">_VBaffle_Price,B28</font>)*C28</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N28</th><td style="text-align:left">=IF(<font color="Blue">M28>0,(<font color="Red">L28 &" "&"Internal V-Baffle"</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M29</th><td style="text-align:left">=INDEX(<font color="Blue">_Angle_Bracket_Price,B29</font>)*C29</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N29</th><td style="text-align:left">=IF(<font color="Blue">M29>0,(<font color="Red">L29 &" "&"Angle Support Bracket"</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M30</th><td style="text-align:left">=INDEX(<font color="Blue">_Weld_Pad_Price,B30</font>)*C30</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N30</th><td style="text-align:left">=IF(<font color="Blue">M30>0,(<font color="Red">L30 &" "&"Weld Pad"</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M31</th><td style="text-align:left">=IF(<font color="Blue">L31=TRUE,Lifting_Lug_Price*C31,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N31</th><td style="text-align:left">=IF(<font color="Blue">L31,"Lifting Lugs",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M32</th><td style="text-align:left">=IF(<font color="Blue">L32=TRUE,Level_Eye_Price*C32,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N32</th><td style="text-align:left">=IF(<font color="Blue">L32,"Level Eyes",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M33</th><td style="text-align:left">=IF(<font color="Blue">L33=TRUE,CRN_Price,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N33</th><td style="text-align:left">=IF(<font color="Blue">L33,"CRN",""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M34</th><td style="text-align:left">=IF(<font color="Blue">L34=TRUE,Oil_Pot_Heater_Price,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N34</th><td style="text-align:left">=IF(<font color="Blue">L34,"Oil Pot Heater",""</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">_250lb10in_Saddles</th><td style="text-align:left">=Accessories!$M$8:$M$16</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">_250lb12inlegs</th><td style="text-align:left">=Accessories!$I$8:$I$16</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">_250lbAdditional_Leg_Length</th><td style="text-align:left">=Accessories!$J$8:$J$16</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">_250lbDemister_Pad</th><td style="text-align:left">=Accessories!$Q$8:$Q$16</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">_250lbFlush_Saddles</th><td style="text-align:left">=Accessories!$L$8:$L$16</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">_250lbPerf_Plates</th><td style="text-align:left">=Accessories!$N$8:$N$16</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">_250lbRound_Plates</th><td style="text-align:left">=Accessories!$P$8:$P$16</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">_250lbSquare_Plates</th><td style="text-align:left">=Accessories!$O$8:$O$16</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">_250lbStand_Pipe</th><td style="text-align:left">=Accessories!$K$8:$K$16</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">_Angle_Bracket_Price</th><td style="text-align:left">=Accessories!$X$2:$X$6</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">_VBaffle_Price</th><td style="text-align:left">=Accessories!$V$2:$V$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">_Vortex_Prices</th><td style="text-align:left">=Accessories!$T$2:$T$13</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">_Weld_Pad_Price</th><td style="text-align:left">=Accessories!$Z$2:$Z$5</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">CRN_Price</th><td style="text-align:left">=Accessories!$AC$2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Level_Eye_Price</th><td style="text-align:left">=Accessories!$AB$2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Lifting_Lug_Price</th><td style="text-align:left">=Accessories!$AA$2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Oil_Pot_Heater_Price</th><td style="text-align:left">=Accessories!$AD$2</td></tr></tbody></table></td></tr></table><br />
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

jfutrell73

New Member
Joined
Aug 9, 2013
Messages
14
I've looked over that info and do not see how that can do what I am trying to accomplish. I am very new at macros.
Any further assistance would be appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,488
Messages
5,468,891
Members
406,619
Latest member
bilestones

This Week's Hot Topics

Top