lfalla
09-23-2016, 05:49 AM
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.
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.