Sort using the active cell value

[lfalla]

Hello everyone.

Main idea: create a table where people can sort information selecting a predefined word (from many) and pressing a button. With that, I tried to create a code where my idea was to make excel identify the activecell (predefined word), copy and paste the active selection to a random cell, put that value in a variable and use the variable's value to sort stuff. I've tried to adapt the code created by the Record Macro tool, but to no avail.

Sub Sort_Result1()
    
    Dim r As Range
    Dim c

    Set r = ActiveCell
    ActiveSheet.r.Copy <--- First error (of many, so it seems...)
    ActiveSheet.Paste("AJ12")
    
    Set c = Range("AJ12")
                
    Application.AddCustomList c
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("F5:F25"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= c _
        , DataOption:=xlSortNormal
    
    With ActiveSheet.Sort
        .SetRange Range("B4:F25")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    
    End With

End Sub

Any inputs, thoughts?

Thanks, and sorry if this question is way too stupid.

[Paul_Hossler]

selecting a predefined word (from many)

Would the predefined word be the column header to sort by?

[lfalla]

Would the predefined word be the column header to sort by?

Hey!

No. I made a column of "key words", that I called "predefined words to sort", located from L5:L44, in a total of 40 possibilities (two examples of predefined words: Ecology and Environmental Science).

[lfalla]

After almost 2 days trying to make it work, I finally made it. Yay

I'm posting here the rewriten code, for future reference if anyone needs:

Sub Sort()
    Dim ws As Worksheet
    Dim act
    Dim src
    
    
    Set ws = ActiveWorkbook.ActiveSheet
    Set act = ActiveCell
    src = act
    
    ws.Range("AJ12").Value = src
       
    ws.AutoFilter.Sort.SortFields.Clear
    ws.AutoFilter.Sort.SortFields.Add Key:=Range( _
    "F5:f86"), SortOn:=xlSorteOnValues, Order:=xlAscending, _
    CustomOrder:=CVar(src), DataOption:=xlSortNormal
            
    With ws.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub