excelvbaautofilterexcel-tables

Excel VBA - ShowAllData of table fails


I have this longer piece of VBA (see also my former question: https://stackoverflow.com/questions/53206850/copy-rows-which-match-criteria-into-two-or-more-different-sheets-in-excel-with-v).

One particular piece doesn't work properly. I want a table to ShowAllData as soon as its former actions from the module have been taken. This code I made so far, works fine as long as I run it when I'm from one of the cells of the table. As soon as I run it from another place on the worksheet, it won't show all data again. It keeps hanging in the filtermode of the table.

   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

Solution

  • It should be:

    Worksheets("Orders").ListObjects("Orders").Autofilter.showalldata