Extract rows with value >=1 to another sheet

[bobbieatendido]

Hi,

I would like to ask for help. I need to extract rows when it meets the criteria >=1.

EX:

Complete Product Sheet:[TABLE="width: 500"]
<tbody>[TR]
[TD]ProdCode[/TD]
[TD]ProdDesc[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Prod1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Prod2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Prod3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1004[/TD]
[TD]Prod4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1005[/TD]
[TD]Prod5[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Order Product Sheet:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ProdCode[/TD]
[TD]ProdDesc[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Prod2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Prod3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1005[/TD]
[TD]Prod5[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Thank you

 

[Michael M]

Maybe this

Sub MM1()
 With Sheets("Complete Product Sheet").UsedRange
            .AutoFilter
            .AutoFilter field:=3, Criteria1:=">=1"
            .Resize(.Rows.Count).SpecialCells(xlCellTypeVisible).Copy Sheets("Order Product Sheet").Range("A1")
            .AutoFilter
    End With
End Sub

 

[bobbieatendido]

Hi Michael,

Thank you for your reply.
Its not working.

I'll complete my info, sorry about that.
So my "Complete Product Sheet" looks like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD][TABLE="width: 26"]
<tbody>[TR]
[TD]CN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD]Dept[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 63"]
<tbody>[TR]
[TD]Group[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 47"]
<tbody>[TR]
[TD] 010118 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD] Std Cost [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 61"]
<tbody>[TR]
[TD]SOURCE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 222"]
<tbody>[TR]
[TD]Description[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 54"]
<tbody>[TR]
[TD]Price [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD]1001
[/TD]
[TD]Prod1[/TD]
[TD]100[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD]1002[/TD]
[TD]Prod2[/TD]
[TD]200
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD]1003
[/TD]
[TD]Prod3[/TD]
[TD]300[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD]1004[/TD]
[TD]Prod4[/TD]
[TD]400[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD](info)[/TD]
[TD]1005[/TD]
[TD]Prod5[/TD]
[TD]500[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

also, in Col. L, the Qty "0 1 2 0 1" is a code which is "=vlookup...".

I need to extract only the row with ">=1" to another sheet "Order Product Sheet".
Which will be CN 3, 4 and 6.

Thank you.

 

[Michael M]

So, you want the dat that is greater than 1 in column L to be copied to the other sheet ??

Sub MM1()
 With Sheets("Complete Product Sheet").UsedRange
            .AutoFilter
            .AutoFilter field:=12, Criteria1:=">=1"
            .Resize(.Rows.Count).SpecialCells(xlCellTypeVisible).Copy Sheets("Order Product Sheet").Range("A1")
            .AutoFilter
    End With
End Sub

 

[bobbieatendido]

Hi Michael,

Where do i put the code?

VBAProject

Sheet1 (Complete Product Sheet)
Sheet2 (Order Product Sheet)

or should i create a macro and put the code there?

Thank you so much for your help.

 

[Michael M]

In The "ThisWorkbook" module or Insert>>Module>>Paste in RH window