Copy rows which match criteria into two or more di

Question

Is there anyone willing to help a really self-taught beginner (with a lot of enthusiasm to learn this stuff)?

For a stock and order file I'm looking for a way to copy the rows in a table which match 1 criteria to another sheet. At the same time I want all other rows (which do not match the 1 criteria) to be copied into another sheet.

I made it to the point where I can copy the rows of table ORDERS on sheet ORDERS, to the table INSTOCKORDERS on sheet INSTOCKORDERS. But what I really want is to make two different sheets out of all the orders. One with the items that are ordered and are in stock. And another sheet with the orders/items that are NOT in stock. After this action took place, it should empty the ORDERS-table. And after that, the next time it should place the ORDERS into the tables INSTOCKORDERS and NOSTOCKORDERS below their last row.

What I meant to do with the sorting is to put all the items with a stockquantity of 0 on top of the table, which I thought could make it easier to copy all the ones with 0 items to NOSTOCKORDERS and all the other ones to INSTOCKORDERS. But I have no clue on how to make this a neat working action let alone, how to start (sorry for my beginners ignorance...) I went through some former questions, ran into AutoFilter, but didn't get it...

Sub CopyOrders()

'Sorting column STOCK in ORDERS from a-z
    Worksheets("Orders").ListObjects("Orders").Sort. _
        SortFields.Clear
    Worksheets("Orders").ListObjects("Orders").Sort. _
        SortFields.Add2 Key:=Range("Orders[[#All],[STOCK]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Worksheets("Orders").ListObjects("Orders").Sort
        .Apply
    End With

'Copying the table ORDERS to INSTOCKORDERS
Range("Orders").Copy Range("InStockOrders")

End Sub

Answer 1

Update: Answer 2: I think I found the error in all this. What is does so far, is copying the right rows at that moment. But it copies with the formulas! This means when the original table (Orders) is being sorted in a different way, or rows get deleted or anything else, the copied rows in "...StockOrders" are showing different values because of there formulas! What I need is a little help... How to copy the values instead of the formulas?

Answer 1: Thanks to Curtis000 it's getting somewhere. I now have a piece of VBA which makes a copy of items which are in stock. It gets copied to the sheet "InStockOrders" to the table with the same name. However there's one minor problem going on.

The firts 5 columns are copied with the right values in it. But the 6th column and further get the values copied which belong to the first to rows of my table "ORDERS".

    Sub FilterInStock()

Worksheets("Orders").Range("B2,I2").AutoFilter Field:=6, Criteria1:="<>" & "0", VisibleDropDown:=True
Worksheets("Orders").Range("ORDERS").SpecialCells(xlCellTypeVisible).Copy Range("InStockOrders")

If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
End If

End Sub

But the table NoStockOrders has a perfectly right copy of the ones that should be there. Up until now I'm running the two VBA's separately. In this sample: first the FilterInStock and then the FilterNoStock.

    Sub FilterNoStock()

Worksheets("Orders").Range("B2,I2").AutoFilter Field:=6, Criteria1:="0", VisibleDropDown:=True
Worksheets("Orders").Range("ORDERS").SpecialCells(xlCellTypeVisible).Copy Range("NoStockOrders")

If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
End If

End Sub

Orders-table

InStockOrders-table

NoStockOrders-table

Answer 2

Finaly got a good working system.

    Sub CopyOrders()

'Sorting column STOCK in ORDERS from A-Z
    Worksheets("Orders").ListObjects("Orders").Sort. _
        SortFields.Clear
    Worksheets("Orders").ListObjects("Orders").Sort. _
        SortFields.Add2 Key:=Range("Orders[[#All],[STOCK]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Worksheets("Orders").ListObjects("Orders").Sort
        .Apply
    End With

'Delete all rows from table NoStockOrders
On Error Resume Next
Worksheets("NoStockOrders").ListObjects("NoStockOrders").DataBodyRange.EntireRow.Delete

'Copy all orders which have no stock to
'the sheet NoStockOrders
Worksheets("Orders").Range("ORDERS").AutoFilter _
    Field:=6, Criteria1:="0", VisibleDropDown:=True
On Error Resume Next
Worksheets("Orders").Range("ORDERS").SpecialCells _
    (xlCellTypeVisible).Copy
    Range("NoStockOrders").PasteSpecial _
    Paste:=xlPasteValues

If Worksheets("Orders").ListObjects("Orders").FilterMode Then
    Worksheets("Orders").AutoFilter.ShowAllData
End If

'Sorting column STOCK in ORDERS from Z-A
    Worksheets("Orders").ListObjects("Orders").Sort. _
        SortFields.Clear
    Worksheets("Orders").ListObjects("Orders").Sort. _
        SortFields.Add2 Key:=Range("Orders[[#All],[STOCK]]"), SortOn:= _
        xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With Worksheets("Orders").ListObjects("Orders").Sort
        .Apply
    End With

'Delete all rows from table InStockOrders
On Error Resume Next
Worksheets("InStockOrders").ListObjects("InStockOrders").DataBodyRange.EntireRow.Delete

'Copy all orders which have no stock to
'the sheet InStockOrders
Worksheets("Orders").Range("ORDERS").AutoFilter _
    Field:=6, Criteria1:=">0", VisibleDropDown:=True
On Error Resume Next
Worksheets("Orders").Range("ORDERS").SpecialCells _
    (xlCellTypeVisible).Copy
    Range("InStockOrders").PasteSpecial _
    Paste:=xlPasteValues

If Worksheets("Orders").FilterMode Then
    Worksheets("Orders").ShowAllData
End If

'Copying the table ORDERS to INSTOCKORDERS
'Range("Orders").Copy Range("InStockOrders")

End Sub

Keep in mind: running this from outside the Orders-table doesn't work properly! It won't clear the filtermode in the table then. (follow my new question: enter link description here)

Answer 3

You are right, the AutoFilter function is what you want. Autofilter needs a criteria to filter for, and then needs to be told which column (field) to find the criteria in. Then you need to copy the visible cells remaining, after you filter. In the code below, replace the Workbooks, Worksheets, and Range with what you need for your workbook:

Sub FilterNoStock()
Workbooks.Worksheets.Range.AutoFilter Field=5, Criteria1:="0", VisibleDropDown:=True
Workbooks.Worksheets.Range.SpecialCells(xlCellTypeVisible).Copy
End Sub

In the same way, you can filter for all but 1 value:

Sub FilterInStock()
Workbooks.Worksheets.Range.AutoFilter Field=5, Criteria1:="<>" & "0", VisibleDropDown:=True
Workbooks.Worksheets.Range.SpecialCells(xlCellTypeVisible).Copy
End Sub

I would also recommend pairing this with the Rows.Count function to be able to dynamically copy only the rows that contain data, and paste it at the end of your lists in your InStock and NoStock sheets. With the Rows.Count method, you make VBA count how many rows you have, set that to a variable, then use that variable to declare the length of your range. Right now you are copying the entire worksheet, which is going to be problematic if you are trying to compile multiple orders in your InStock and NoStock sheets, as pasting will overwrite all your data from previous orders: Description of Rows.Count