Sort using the active cell value

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. :(

Code:

---

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.

Quote:

---

selecting a predefined word (from many)

---

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

Quote:

---

Originally Posted by Paul_Hossler

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).

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:

Code:

---

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

---