CODEWITHSUNDEEP
vbaexcel
Matt G
Matt G

Reputation: 59

Macro changes data to overwrite previous data

I recorded a macro to pick the top ten largest trades sorted first by ten buys then by ten sells. After sorting data according to column D it copies the trade information and pastes it in another cell.
Then it sorts by column E to get the largest sells, and copies the same data range to paste in another cell.
The problem is that it will copy the wrong information because it can't sort the data by column D and E at the same time. How do I get the macro to copy and paste the correct info?

Sub ttt()
'
' ttt Macro
' top ten trades output
'
' Keyboard Shortcut: Ctrl+Shift+T


' buys

    Rows("3:3").Select
    Selection.AutoFilter

    ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range("D3" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A5:I14").Select
    Selection.Copy
    Range("K3").Select
    ActiveSheet.paste
    Application.CutCopyMode = False


' sells



    ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range("E3" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A5:I14").Select
    Selection.Copy
    Range("u3").Select
    ActiveSheet.paste
    Application.CutCopyMode = False

End Sub

Upvotes: 0

Views: 385

Answers (2)

Vityata
Vityata

Reputation: 43575

With a big sign, like in the World Wrestling Federation - do not do this at home - you may try like this:

Sub ttt()
'
' ttt Macro
' top ten trades output
'
' Keyboard Shortcut: Ctrl+Shift+T


' buys

    Rows("3:3").Select
    Selection.AutoFilter

    ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range("D3" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A5:I14").Select
    Selection.Copy
    Range("K3").Select
    ActiveSheet.paste
    Application.CutCopyMode = False


' sells

    Rows("3:3").AutoFilter
    Rows("3:3").AutoFilter



    ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range("E3" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A5:I14").Select
    Selection.Copy
    Range("u3").Select
    ActiveSheet.paste
    Application.CutCopyMode = False

End Sub

I have added and removed the autofilter, so it should be working.

Upvotes: 2

YowE3K
YowE3K

Reputation: 23974

If you record these steps using the Macro Recorder, you will find that it achieves it by simply including the following line between the two sorts:

ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear

In your case that would be 

Activesheet.AutoFilter.Sort.SortFields.Clear

and should be placed just before trying to .Add the new SortField, i.e. the one for column E. (The Macro Recorder also inserts the line before the first Add, just to be safe.)

Upvotes: 1

Related Questions