Extract rows with value >=1 to another sheet

bobbieatendido

New Member
Joined
Jul 13, 2016
Messages
9
Hi,

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

EX:

Complete Product Sheet:
ProdCodeProdDescQty
1001Prod10
1002Prod21
1003Prod32
1004Prod40
1005Prod51

<tbody>
</tbody>

Order Product Sheet:
ProdCodeProdDescQty
1002Prod21
1003Prod32
1005Prod51

<tbody>
</tbody>

Thank you
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
Maybe this

Code:
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

New Member
Joined
Jul 13, 2016
Messages
9
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:


ABCDEFGHIJKL
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
Prod11000
3(info)(info)(info)(info)(info)(info)(info)1002Prod2200
1
4(info)(info)(info)(info)(info)(info)(info)1003
Prod33002
5(info)(info)(info)(info)(info)(info)(info)1004Prod44000
6(info)(info)(info)(info)(info)(info)(info)1005Prod55001

<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.
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
So, you want the dat that is greater than 1 in column L to be copied to the other sheet ??

Code:
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

New Member
Joined
Jul 13, 2016
Messages
9
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
In The "ThisWorkbook" module or Insert>>Module>>Paste in RH window
 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,124
Members
409,561
Latest member
ay123

This Week's Hot Topics

Top