Filtering after sort cancles sort

[wilster2012]

Hello Group, I'm having a problem I hope somebody has a solution to
I am working on a macro that copies data from a pivot table to a new sheet then sorts the data from largest to smallest then filters the data. Everything works fine until I filter the data. After it filters, I lose the sort. Below is the code for the sort and filter subs. Any help would be appreciated

Sub newsort()
    With ActiveWorkbook.Worksheets("NewSort").Sort
        .SetRange Range("A1:D217")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
        Call AutoFilterCopy
  
      End Sub
   
   Sub AutoFilterCopy()
   With Sheet13
            .AutoFilterMode = False
            .Range("E1").AutoFilter
            .Range("a1").AutoFilter Field:=5, Criteria1:="copy"
           .Range("a1").AutoFilter Field:=6, Criteria1:="FALSE"
    End With
End Sub

Thanks in Advance
Willy

 

[Yongle]

1 Why refer to sheet by sheet name in one procedure and by CodeName in the other?
- it works but could lead to confusion
2 In NewSort the data range is "A1:D217", but AutoFilterCopy refers to fields 5 and 6 (ie columns E & F)
- would expect "A1:F217" (or "A1:X217" where column X is the last column)
3 NewSort does not tell VBA which columns to sort on, so values from the previous sort are applied - is that what you want?
- see below if not (which clears sort settings first and sorts by column B, then column A)
4 NewSort ranges amended so that it can deal with any number of rows (without having to find last row)
- could also use Sheets("NewSort").Range("A1").CurrentRegion if data is contiguous
5 Your AutoFilterCopy procedure does not re-sort the data for me (Excel 2016) and I would not expect it to
- which version of Excel are you using?

Sub NewSort()
    
    With Sheets("[COLOR=#ff0000]NewSort[/COLOR]")
        MsgBox .Range("A1").CurrentRegion.Address(0, 0)
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 Key:=Range("B2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add2 Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange Sheets("NewSort").Range("A:F")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
    Call AutoFilterCopy
End Sub

Sub AutoFilterCopy()
   With [COLOR=#000080]Sheet13[/COLOR]
        .AutoFilterMode = False
        With .Range("A1")
            .AutoFilter
            .AutoFilter Field:=5, Criteria1:="copy"
            .AutoFilter Field:=6, Criteria1:="FALSE"
        End With
    End With
End Sub