Hi
Say i have the following data on sheet1 and i want a vba that searches where cell G1 = 22 and cell H = 47 then copies cell range A1 to E6(the first match) to sheet 2, leaves a blank line open and then copies cells A13 to E18(second match) to sheet 2 and carries on until no more matches are found
Thanks
Jaco
A B C D E F G H
[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl68, width: 64, align: right"]22[/TD]
[TD="class: xl68, width: 64, align: right"]11[/TD]
[TD="class: xl68, width: 64, align: right"]44[/TD]
[TD="class: xl68, width: 64, align: right"]40[/TD]
[TD="class: xl68, width: 64, align: right"]36[/TD]
[TD="class: xl68, width: 64, align: right"]5[/TD]
[TD="class: xl68, width: 64, align: right"]22[/TD]
[TD="class: xl68, width: 64, align: right"]47[/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]35[/TD]
[TD="class: xl68, align: right"]8[/TD]
[TD="class: xl68, align: right"]30[/TD]
[TD="class: xl68, align: right"]26[/TD]
[TD="class: xl68, align: right"]28[/TD]
[TD="class: xl68, align: right"]2536[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]10[/TD]
[TD="class: xl68, align: right"]34[/TD]
[TD="class: xl68, align: right"]16[/TD]
[TD="class: xl68, align: right"]1[/TD]
[TD="class: xl68, align: right"]13[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]17[/TD]
[TD="class: xl68, align: right"]18[/TD]
[TD="class: xl68, align: right"]7[/TD]
[TD="class: xl68, align: right"]2[/TD]
[TD="class: xl68, align: right"]42[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]45[/TD]
[TD="class: xl68, align: right"]5[/TD]
[TD="class: xl68, align: right"]33[/TD]
[TD="class: xl68, align: right"]12[/TD]
[TD="class: xl68, align: right"]31[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]11[/TD]
[TD="class: xl68, align: right"]33[/TD]
[TD="class: xl68, align: right"]17[/TD]
[TD="class: xl68, align: right"]45[/TD]
[TD="class: xl68, align: right"]18[/TD]
[TD="class: xl68, align: right"]5[/TD]
[TD="class: xl68, align: right"]25[/TD]
[TD="class: xl68, align: right"]46[/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]2[/TD]
[TD="class: xl68, align: right"]12[/TD]
[TD="class: xl68, align: right"]44[/TD]
[TD="class: xl68, align: right"]36[/TD]
[TD="class: xl68, align: right"]31[/TD]
[TD="class: xl68, align: right"]2633[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]26[/TD]
[TD="class: xl68, align: right"]16[/TD]
[TD="class: xl68, align: right"]13[/TD]
[TD="class: xl68, align: right"]34[/TD]
[TD="class: xl68, align: right"]30[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]22[/TD]
[TD="class: xl68, align: right"]5[/TD]
[TD="class: xl68, align: right"]28[/TD]
[TD="class: xl68, align: right"]42[/TD]
[TD="class: xl68, align: right"]1[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]35[/TD]
[TD="class: xl68, align: right"]10[/TD]
[TD="class: xl68, align: right"]7[/TD]
[TD="class: xl68, align: right"]8[/TD]
[TD="class: xl68, align: right"]40[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]30[/TD]
[TD="class: xl68, align: right"]13[/TD]
[TD="class: xl68, align: right"]18[/TD]
[TD="class: xl68, align: right"]8[/TD]
[TD="class: xl68, align: right"]7[/TD]
[TD="class: xl68, align: right"]5[/TD]
[TD="class: xl68, align: right"]22[/TD]
[TD="class: xl68, align: right"]47[/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]1[/TD]
[TD="class: xl68, align: right"]35[/TD]
[TD="class: xl68, align: right"]10[/TD]
[TD="class: xl68, align: right"]28[/TD]
[TD="class: xl68, align: right"]36[/TD]
[TD="class: xl68, align: right"]4337[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]42[/TD]
[TD="class: xl68, align: right"]33[/TD]
[TD="class: xl68, align: right"]12[/TD]
[TD="class: xl68, align: right"]16[/TD]
[TD="class: xl68, align: right"]44[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]11[/TD]
[TD="class: xl68, align: right"]45[/TD]
[TD="class: xl68, align: right"]5[/TD]
[TD="class: xl68, align: right"]34[/TD]
[TD="class: xl68, align: right"]40[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]2[/TD]
[TD="class: xl68, align: right"]22[/TD]
[TD="class: xl68, align: right"]26[/TD]
[TD="class: xl68, align: right"]31[/TD]
[TD="class: xl68, align: right"]17[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"]
[/TD]
[TD="class: xl68"][/TD]
[/TR]
</tbody>[/TABLE]
Say i have the following data on sheet1 and i want a vba that searches where cell G1 = 22 and cell H = 47 then copies cell range A1 to E6(the first match) to sheet 2, leaves a blank line open and then copies cells A13 to E18(second match) to sheet 2 and carries on until no more matches are found
Thanks
Jaco
A B C D E F G H
[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl68, width: 64, align: right"]22[/TD]
[TD="class: xl68, width: 64, align: right"]11[/TD]
[TD="class: xl68, width: 64, align: right"]44[/TD]
[TD="class: xl68, width: 64, align: right"]40[/TD]
[TD="class: xl68, width: 64, align: right"]36[/TD]
[TD="class: xl68, width: 64, align: right"]5[/TD]
[TD="class: xl68, width: 64, align: right"]22[/TD]
[TD="class: xl68, width: 64, align: right"]47[/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]35[/TD]
[TD="class: xl68, align: right"]8[/TD]
[TD="class: xl68, align: right"]30[/TD]
[TD="class: xl68, align: right"]26[/TD]
[TD="class: xl68, align: right"]28[/TD]
[TD="class: xl68, align: right"]2536[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]10[/TD]
[TD="class: xl68, align: right"]34[/TD]
[TD="class: xl68, align: right"]16[/TD]
[TD="class: xl68, align: right"]1[/TD]
[TD="class: xl68, align: right"]13[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]17[/TD]
[TD="class: xl68, align: right"]18[/TD]
[TD="class: xl68, align: right"]7[/TD]
[TD="class: xl68, align: right"]2[/TD]
[TD="class: xl68, align: right"]42[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]45[/TD]
[TD="class: xl68, align: right"]5[/TD]
[TD="class: xl68, align: right"]33[/TD]
[TD="class: xl68, align: right"]12[/TD]
[TD="class: xl68, align: right"]31[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]11[/TD]
[TD="class: xl68, align: right"]33[/TD]
[TD="class: xl68, align: right"]17[/TD]
[TD="class: xl68, align: right"]45[/TD]
[TD="class: xl68, align: right"]18[/TD]
[TD="class: xl68, align: right"]5[/TD]
[TD="class: xl68, align: right"]25[/TD]
[TD="class: xl68, align: right"]46[/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]2[/TD]
[TD="class: xl68, align: right"]12[/TD]
[TD="class: xl68, align: right"]44[/TD]
[TD="class: xl68, align: right"]36[/TD]
[TD="class: xl68, align: right"]31[/TD]
[TD="class: xl68, align: right"]2633[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]26[/TD]
[TD="class: xl68, align: right"]16[/TD]
[TD="class: xl68, align: right"]13[/TD]
[TD="class: xl68, align: right"]34[/TD]
[TD="class: xl68, align: right"]30[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]22[/TD]
[TD="class: xl68, align: right"]5[/TD]
[TD="class: xl68, align: right"]28[/TD]
[TD="class: xl68, align: right"]42[/TD]
[TD="class: xl68, align: right"]1[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]35[/TD]
[TD="class: xl68, align: right"]10[/TD]
[TD="class: xl68, align: right"]7[/TD]
[TD="class: xl68, align: right"]8[/TD]
[TD="class: xl68, align: right"]40[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]30[/TD]
[TD="class: xl68, align: right"]13[/TD]
[TD="class: xl68, align: right"]18[/TD]
[TD="class: xl68, align: right"]8[/TD]
[TD="class: xl68, align: right"]7[/TD]
[TD="class: xl68, align: right"]5[/TD]
[TD="class: xl68, align: right"]22[/TD]
[TD="class: xl68, align: right"]47[/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]1[/TD]
[TD="class: xl68, align: right"]35[/TD]
[TD="class: xl68, align: right"]10[/TD]
[TD="class: xl68, align: right"]28[/TD]
[TD="class: xl68, align: right"]36[/TD]
[TD="class: xl68, align: right"]4337[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]42[/TD]
[TD="class: xl68, align: right"]33[/TD]
[TD="class: xl68, align: right"]12[/TD]
[TD="class: xl68, align: right"]16[/TD]
[TD="class: xl68, align: right"]44[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]11[/TD]
[TD="class: xl68, align: right"]45[/TD]
[TD="class: xl68, align: right"]5[/TD]
[TD="class: xl68, align: right"]34[/TD]
[TD="class: xl68, align: right"]40[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]2[/TD]
[TD="class: xl68, align: right"]22[/TD]
[TD="class: xl68, align: right"]26[/TD]
[TD="class: xl68, align: right"]31[/TD]
[TD="class: xl68, align: right"]17[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"]
[/TD]
[TD="class: xl68"][/TD]
[/TR]
</tbody>[/TABLE]