Custom Sort in VBA

[lopka]

Dear all,

I have troubles with sorting..

Actually what I need is to sort by row... The row is supposed to be picked up from the drop-down list. Let say in C8..

These factors I have in rows 91:97.

I make from these rows graphs by selecting and filtering in drop-down list..
But I have to have the graphs sorted - descending..
I try to modify some code, but it doesn´t work..

Range("C8").Select
Selection.Copy
ActiveSheet.Range("$A$90:$S$97").AutoFilter Field:=1, Criteria1:=Selection

    
ActiveWorkbook.Worksheets("Branch selection").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Branch selection").Sort.SortFields.Add Key:=Range( _
"A90:S97"), SortOn:=xlSortOnValues, Order:=xlDescending, CustomOrder:=xlmyVar1, Orientation:=xlLeftToRight, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Branch selection").Sort
        .SetRange Range("A90:S97")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
   End With

I´m pretty unhappy because I really try hard..

Could you please anyone help? Thank you very much!

 

[GlennUK]

Can you give an example of actual data, before sorting, and what you'd like it to look like after sorting?

 

[lopka]

Of course..
This is an example of data what I have..

The Fee is the desired state - sorted data..

And for Revenues and Count, I need some custom sort to made Excel do this in the same way, like for Fee..

The file is here:
http://lida-malinova.ic.cz/example.xls

Thank you!

 

[GlennUK]

For your example file, try this:

Private Sub CommandButton1_Click()

Dim myVar1 As String

myVar1 = Range("D2")

ActiveSheet.Range("$A$5:$S$8").AutoFilter
ActiveWorkbook.Worksheets("Example").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Example").Sort.SortFields.Add _
Key:=Range("B5:S8").Offset(Application.Match(myVar1, Range("A6:A8"), 0)).Resize(1) _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Example").Sort
        .SetRange Range("B5:S8")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
   End With
ActiveSheet.Range("$A$5:$S$8").AutoFilter Field:=1, Criteria1:=myVar1

End Sub