VBA to sort automatically in a custom order

[Josh Johansen]

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?

 

[Smallweed]

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,IF(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]

I got to the point where I attempted using the VBA, the if statements were
easy and obsvious, but I think because I am trying to sort a pivot table I am
having issues. I should have mentioned that in my original question, but the
information is in a pivot table and I am trying to sort the data to a column
to the right of the pivot table. Is this even possible?