No error in VBA code but sort not functioning properly

[AlabasterShelf]

Hello all,

I have a spreadsheet that is pulling in query data. My VBA code refreshes the query, populates a formulas in two column, and finally sorts the data.

The query refresh is fine. The formula population is fine. The trouble I am having is that the sorting is not behaving. I don't get an error message. And the filter icon has the little triangle that shows excel thinks the column is sorted. But it is not.

In the specific column of interest, I am trying to remove the blank dates and sort oldest to newest. I used the macro recorder to generate the code seen here. I dropped message box indicators in each line of code, and they all worked fine so I know the code is cycling through properly. But it still isn't sorting. As I mentioned, I don't get any kind of error code with this. Anyone have any ideas?

'sort data
<today(),""late"",""""))"
ActiveWorkbook.Worksheets("OPERATION").ListObjects("OPER").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("OPERATION").ListObjects("OPER").Sort.SortFields.Add _
    Key:=Range("OPER[[#All],[WANT_DATE]]"), SortOn:=xlSortOnValues, Order:= _
    xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("OPERATION").ListObjects("OPER").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ThisWorkbook.Worksheets("OPERATION").ListObjects("OPER").Range.AutoFilter Field:=4, Criteria1:="<>"

</today(),""late"",""""))"

 

[jmacleary]

You didn't paste your code above, but it may be that you need to 'encourage' excel to re-filter. Try adding this line to your code after you have finished setting the filters (replace Sheet1 as appropriate):

<code class="vb spaces">   </code><code class="vb plain">Sheets(</code><code class="vb string">"Sheet1"</code><code class="vb plain">).AutoFilter.ApplyFilter</code>

 

[AlabasterShelf]

Argh! It was pasted in the post creator but didn't translate. Let me try again here. I did try the ApplyFilter line and it still did not work.

'sort data
ThisWorkbook.Worksheets("OPERATION").Activate
ActiveWorkbook.Worksheets("OPERATION").ListObjects("OPER").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("OPERATION").ListObjects("OPER").Sort.SortFields.Add _
    Key:=Range("OPER[[#All],[WANT_DATE]]"), SortOn:=xlSortOnValues, Order:= _
    xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("OPERATION").ListObjects("OPER").Range.AutoFilter Field:=4, Criteria1:="<>"
ThisWorkbook.Worksheets("OPERATION").AutoFilter.ApplyFilter

 

[jmacleary]

You are setting sort criteria, but not actually telling it to sort. Add this line:

 ActiveWorkbook.Worksheets("OPERATION").AutoFilter.Sort.Apply

 

[AlabasterShelf]

You are setting sort criteria, but not actually telling it to sort.

Isn't that what this section is doing? The very last line says .Apply

    With ActiveWorkbook.Worksheets("OPERATION").ListObjects("OPER").Sort       
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[jmacleary]

That does indeed do it - but if you look above at your previous posts, this section of code has not been revealed to me. Please post the full code so I can help further.