Macro to Copy & Paste

[Alex O]

Need a macro that will scan (sheet2) BQ and if >0 copy row BL:BS to Sheet1. Then:
Skip two rows and scan (sheet2) BP and if >0 copy row BL:BS to Sheet1.
Any help is appreciated....nothing I'm try seems to work!
Regards


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 57px"><COL style="WIDTH: 63px"><COL style="WIDTH: 248px"><COL style="WIDTH: 59px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 53px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>BL</TD><TD>BM</TD><TD>BN</TD><TD>BO</TD><TD>BP</TD><TD>BQ</TD><TD>BR</TD><TD>BS</TD></TR><TR style="HEIGHT: 78px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #00ffff">ITEM DATE</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #00ffff">DEBT ID NO</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #00ffff">DEBT CLT REF NO</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #00ffff">DBTR NAME1</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #00ffff">PAID US</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #00ffff">PAID YOU</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #00ffff">DUE US</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #00ffff">DUE YOU</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: left">2/1/2011</TD><TD style="TEXT-ALIGN: left">297754</TD><TD style="TEXT-ALIGN: left">10018424</TD><TD style="TEXT-ALIGN: left">Better Basement Technologies, L.L.C</TD><TD style="TEXT-ALIGN: left">0</TD><TD style="TEXT-ALIGN: left">2560</TD><TD style="TEXT-ALIGN: left">460.8</TD><TD style="TEXT-ALIGN: left">-460.8</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: left">2/25/2011</TD><TD style="TEXT-ALIGN: left">274542</TD><TD style="TEXT-ALIGN: left">75133</TD><TD style="TEXT-ALIGN: left">Rampion Landscaping, L.L.C.</TD><TD style="TEXT-ALIGN: left">0</TD><TD style="TEXT-ALIGN: left">1296.34</TD><TD style="TEXT-ALIGN: left">233.34</TD><TD style="TEXT-ALIGN: left">-233.34</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left">2/1/2011</TD><TD style="TEXT-ALIGN: left">287340</TD><TD style="TEXT-ALIGN: left">10017546</TD><TD style="TEXT-ALIGN: left">Center Cars</TD><TD style="TEXT-ALIGN: left">0</TD><TD style="TEXT-ALIGN: left">1086.11</TD><TD style="TEXT-ALIGN: left">195.49</TD><TD style="TEXT-ALIGN: left">-195.49</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left">2/23/2011</TD><TD style="TEXT-ALIGN: left">324274</TD><TD style="TEXT-ALIGN: left">65620</TD><TD style="TEXT-ALIGN: left">Aur Computer Services</TD><TD style="TEXT-ALIGN: left">0</TD><TD style="TEXT-ALIGN: left">921</TD><TD style="TEXT-ALIGN: left">165.78</TD><TD style="TEXT-ALIGN: left">-165.78</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left">2/23/2011</TD><TD style="TEXT-ALIGN: left">319445</TD><TD style="TEXT-ALIGN: left">68790</TD><TD style="TEXT-ALIGN: left">R-J Mobile Home Repair</TD><TD style="TEXT-ALIGN: left">0</TD><TD style="TEXT-ALIGN: left">802.5</TD><TD style="TEXT-ALIGN: left">144.45</TD><TD style="TEXT-ALIGN: left">-144.45</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left">2/17/2011</TD><TD style="TEXT-ALIGN: left">310440</TD><TD style="TEXT-ALIGN: left">64712</TD><TD style="TEXT-ALIGN: left">Shortridge Law Offices, L.L.C.</TD><TD style="TEXT-ALIGN: left">0</TD><TD style="TEXT-ALIGN: left">750</TD><TD style="TEXT-ALIGN: left">135</TD><TD style="TEXT-ALIGN: left">-135</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left">2/14/2011</TD><TD style="TEXT-ALIGN: left">319357</TD><TD style="TEXT-ALIGN: left">27688</TD><TD style="TEXT-ALIGN: left">George Inzer & Son</TD><TD style="TEXT-ALIGN: left">0</TD><TD style="TEXT-ALIGN: left">701.25</TD><TD style="TEXT-ALIGN: left">126.22</TD><TD style="TEXT-ALIGN: left">-126.22</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left">2/24/2011</TD><TD style="TEXT-ALIGN: left">315214</TD><TD style="TEXT-ALIGN: left">10022400</TD><TD style="TEXT-ALIGN: left">Bmw Builders Ii</TD><TD style="TEXT-ALIGN: left">0</TD><TD style="TEXT-ALIGN: left">640.48</TD><TD style="TEXT-ALIGN: left">115.28</TD><TD style="TEXT-ALIGN: left">-115.28</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left">2/1/2011</TD><TD style="TEXT-ALIGN: left">315495</TD><TD style="TEXT-ALIGN: left">10015327</TD><TD style="TEXT-ALIGN: left">Ars Contractors, L.L.C.</TD><TD style="TEXT-ALIGN: left">0</TD><TD style="TEXT-ALIGN: left">576.78</TD><TD style="TEXT-ALIGN: left">103.82</TD><TD style="TEXT-ALIGN: left">-103.82</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left">2/24/2011</TD><TD style="TEXT-ALIGN: left">324272</TD><TD style="TEXT-ALIGN: left">61228</TD><TD style="TEXT-ALIGN: left">Image Collision Center</TD><TD style="TEXT-ALIGN: left">0</TD><TD style="TEXT-ALIGN: left">541</TD><TD style="TEXT-ALIGN: left">97.38</TD><TD style="TEXT-ALIGN: left">-97.38</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left">2/1/2011</TD><TD style="TEXT-ALIGN: left">315539</TD><TD style="TEXT-ALIGN: left">10014032</TD><TD style="TEXT-ALIGN: left">Delicious Bakery-Cafe</TD><TD style="TEXT-ALIGN: left">0</TD><TD style="TEXT-ALIGN: left">524.59</TD><TD style="TEXT-ALIGN: left">94.42</TD><TD style="TEXT-ALIGN: left">-94.42</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left">2/1/2011</TD><TD style="TEXT-ALIGN: left">297791</TD><TD style="TEXT-ALIGN: left">65181</TD><TD style="TEXT-ALIGN: left">American Satellite, L.L.C.</TD><TD style="TEXT-ALIGN: left">0</TD><TD style="TEXT-ALIGN: left">507.6</TD><TD style="TEXT-ALIGN: left">91.36</TD><TD style="TEXT-ALIGN: left">-91.36</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: left">2/17/2011</TD><TD style="TEXT-ALIGN: left">322765</TD><TD style="TEXT-ALIGN: left">61816</TD><TD style="TEXT-ALIGN: left">Henry'S Professional Automotive Det</TD><TD style="TEXT-ALIGN: left">0</TD><TD style="TEXT-ALIGN: left">470</TD><TD style="TEXT-ALIGN: left">84.6</TD><TD style="TEXT-ALIGN: left">-84.6</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: left">2/1/2011</TD><TD style="TEXT-ALIGN: left">312010</TD><TD style="TEXT-ALIGN: left">10016593</TD><TD style="TEXT-ALIGN: left">Basta'S</TD><TD style="TEXT-ALIGN: left">0</TD><TD style="TEXT-ALIGN: left">467.82</TD><TD style="TEXT-ALIGN: left">84.2</TD><TD style="TEXT-ALIGN: left">-84.2</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: left">2/1/2011</TD><TD style="TEXT-ALIGN: left">297980</TD><TD style="TEXT-ALIGN: left">10015917</TD><TD style="TEXT-ALIGN: left">Dependable Contractors, L.L.C.</TD><TD style="TEXT-ALIGN: left">0</TD><TD style="TEXT-ALIGN: left">431.25</TD><TD style="TEXT-ALIGN: left">77.62</TD><TD style="TEXT-ALIGN: left">-77.62</TD></TR></TBODY></TABLE>

 

[Peter_SSs]

Try this in a copy of your workbook.

Tweaks could depend on things like
- is there is data already in Sheet1?
- if so, is that data to be kept & added to or replaced?
- did you want the headings copied as well?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CopyData()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet2")<br>        <SPAN style="color:#00007F">With</SPAN> Intersect(.UsedRange, .Columns("BL:BS"))<br>            .AutoFilter Field:=6, Criteria1:=">0"<br>            .Offset(1).Copy Destination:=Sheets("Sheet1") _<br>                .Range("A" & Rows.Count).End(xlUp).Offset(1)<br>            .AutoFilter<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Alex O]

Wow - Thanks for the quick reply...this is almost what I was aiming for. If I could get the headers to copy as well that would be great! Also, is it possible to have a blank row between the BP & BQ date sets?
Regards

 

[Alex O]

Peter,
I just noticed it's not going to BP and searcing. I was hoping to have it do exactly what it's doing now, and then repeat the process in column BP....

 

[Peter_SSs]

.. is it possible to have a blank row between the BP & BQ date sets?

With reference to the sample data given, could you explain exactly what you mean by this?

 

[Peter_SSs]

Peter,
I just noticed it's not going to BP and searcing. I was hoping to have it do exactly what it's doing now, and then repeat the process in column BP....

With reference to the sample data given, could you explain exactly what you mean by this?

OK, I misread the original question and think I understand now. Give me a few minutes.

 

[Peter_SSs]

Try this.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CopyData()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet2")<br>        <SPAN style="color:#00007F">With</SPAN> Intersect(.UsedRange, .Columns("BL:BS"))<br>            .AutoFilter Field:=6, Criteria1:=">0"<br>            .Copy Destination:=Sheets("Sheet1") _<br>                .Range("A" & Rows.Count).End(xlUp).Offset(1)<br>            .AutoFilter<br>            .AutoFilter Field:=5, Criteria1:=">0"<br>            .Copy Destination:=Sheets("Sheet1") _<br>                .Range("A" & Rows.Count).End(xlUp).Offset(3)<br>            .AutoFilter<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Alex O]

Perfect! If you ever find your way to <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on"><st1:City w:st="on">New Orleans</st1:City></st1lace> let me know and I’ll treat to a Mint Julep!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Take Care

 

[Peter_SSs]

Perfect! If you ever find your way to <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on"><st1:City w:st="on">New Orleans</st1:City></st1lace> let me know and I’ll treat to a Mint Julep!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Take Care

Cheers, I'll keep it in mind.