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