VBA to sort automatically in a custom order
Here's a quick (and dirty!) solution. Type the following formula into R2
=IF(Q2="M",1,IF(Q2="T",2,IF(Q2="W",3,IF(Q2="R",4,I F(Q2="F",5,IF(Q2="S",6,IF(Q2="Y",7,"")))))))
Then copy it way down beyond the data and hide the column.
The following macro will sort on column R (I've included two alternatives,
the first assumes your table has no headings, the second assumes it does):
Sub mySort()
Range("A1").CurrentRegion.Sort Key1:=Range("R1"), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
' Range("A1").CurrentRegion.Sort Key1:=Range("R2"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
Then create a button on the sheet (from Forms toolbar) or in a toolbar and
attach this macro.
"Josh Johansen" wrote:
I have a list of data where in the very last column of data (Q), users input
a letter for a day of the week they establish. M for Monday, T for tuesday,
R for thursday, etc. What I would like to do is generate an excel button
that will automatically select the data I want to sort, and then sort it
automatically without them having to go to the toolbar and select data
filter...etc. If possible I would like to put the days in order, Monday thru
Friday, but if not, alphabetical would work. Any ideas?
|