Xtykutl

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

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

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