Filtering after sort cancles sort

wilster2012

New Member
Joined
Jul 8, 2016
Messages
7
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

Code:
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
1 Why refer to sheet by sheet name in one procedure and by CodeName in the other?
- it works but could lead to confusion :confused:
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?

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top