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:

ProdCode	ProdDesc	Qty
1001	Prod1	0
1002	Prod2	1
1003	Prod3	2
1004	Prod4	0
1005	Prod5	1

<tbody>
</tbody>

Order Product Sheet:

ProdCode	ProdDesc	Qty
1002	Prod2	1
1003	Prod3	2
1005	Prod5	1

<tbody>
</tbody>

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:

A	B	C	D	E	F	G	H	I	J	K	L
CN <tbody> </tbody>	Dept <tbody> </tbody>	Group <tbody> </tbody>			010118 <tbody> </tbody>	Std Cost <tbody> </tbody>	SOURCE <tbody> </tbody>	Code <tbody> </tbody>	Description <tbody> </tbody>	Price <tbody> </tbody>	QTY
2	(info)	(info)	(info)	(info)	(info)	(info)	(info)	1001	Prod1	100	0
3	(info)	(info)	(info)	(info)	(info)	(info)	(info)	1002	Prod2	200	1
4	(info)	(info)	(info)	(info)	(info)	(info)	(info)	1003	Prod3	300	2
5	(info)	(info)	(info)	(info)	(info)	(info)	(info)	1004	Prod4	400	0
6	(info)	(info)	(info)	(info)	(info)	(info)	(info)	1005	Prod5	500	1

<tbody>
</tbody>

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