Automatic Sort After Filter

Hello Everybody,

I'm trying to write one macro that filters a set of data based on 2 criteria and then automatically sorts the filtered data by date. The filter works fine but when I then try to write code that handles the automatic sort, I get the following message when I run the macro: "Run-Time Error 1004: the sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank."

I realize it would only take one or two more clicks to sort this data manully after the macro is run, but the lazy person in me is trying to eliminate this step. Does anybody have any suggestions as to why the sort is not working? I parsed basic sorting code, but it hasn't worked. The other part of this that is causing me trouble is that the number of filtered rows will vary, so I'm only needing to sort the visible range.

Any help you can offer would be greatly appreciated... Thanks in advance!!

Re: Automatic Sort After Filter

Thanks Robert for the quick reply - I still seem to be getting the same error, whether I sort it before or after the filter.

I'm thinking this can be solved by applying an AutoFilter Sort in VBA. Is anyone familiar with this? I know how to apply autofilter criteria in VBA but can't seem to transfer that to a sort.

I welcome any and all suggestions... thank you.

Re: Automatic Sort After Filter

Here's a snippet of the code I'm using (does not include the autofilter part). When run, this returns the error mentioned in above post.

Sub CommandButton15_Click()
    
Worksheets("DataSheet2").Select


With ActiveSheet.Range(("A1:O1"), ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count))
    .Sort Key1:=Range("G1"), Order1:=xlAscending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
    End With
    
End Sub

Re: Automatic Sort After Filter

Thanks Kenneth - were you able to run this wothout getting the error? It's still coming up, so I'm wondering if there's something else wrong with the actual worksheet (although I've tried it on several others and keep getting the same error message).

Also, I apologize - I'm still fairly new to VBA - but can you give me a little more detail regarding the debug.print lines. What do they do and where are they supposed to go? Thanks so much!

Re: Automatic Sort After Filter

Here's a sample. Try running the macro on Sheet1 and see if you get the following error that I always get when I run the macro:

"Run-Time Error 1004: the sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank."

Re: Automatic Sort After Filter

I think the issue is the Command Button is on a different sheet from the data set. For some reason, I think its using the wrong sheet to get the sort reference from.

Based on the code above, anyone have any suggestions as to how to go about getting it to stop doing this?

Re: Automatic Sort After Filter

Figured it out... here's the final code. Thought I had tried this earlier but I guess not. Thanks everyone for your input.

Sub CommandButton1_Click()


Dim wsSheet As Worksheet


Set wsSheet = Worksheets("DataSheet2")


With wsSheet.Range(("A3:N3"), wsSheet.UsedRange.Rows(Worksheets("DataSheet2").UsedRange.Rows.Count))
    .Sort Key1:=wsSheet.Range("G3"), Order1:=xlAscending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
    End With
    
Worksheets("DataSheet2").Select


End Sub