Macro to Sort Automatically Based on Contents of Drop-down Box

[Ashleigh K.]

Hello All,

I am trying to create a capability where a user of my spreadsheet would be
able to choose the primary sort key via a drop-down box (eg last name, first
name, etc) and then have the spreadsheet sort automatically. This drop down
box is located in cell b12. My table has headings(a1313) matching the
options in the drop down box. I have parts of the macro written, but I am
having trouble tying it all together. Basically, I would like the macro to
look at cell b12, match it with the appropriate heading and then sort
primarily based on that column of information. Below is an example of what I
have been working on (although it does not work)


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)



If Target.Address = "$B$12" Then



If Range(B13).Value = Target.Value Then

Range("A1425").Select

Selection.Sort Key1:=Range("B14"), Order1:=xlAscending, Header:=xlGuess,
_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End if



If Range(D13).Value = Target.Value Then

Range("A1425").Select

Selection.Sort Key1:=Range("D14"), Order1:=xlAscending, Header:=xlGuess,
_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End if



If Range(E13).Value = Target.Value Then

Range("A1425").Select

Selection.Sort Key1:=Range("E14"), Order1:=xlAscending, Header:=xlGuess,
_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End if



If Range(H13).Value = Target.Value Then

Range("A1425").Select

Selection.Sort Key1:=Range("H14"), Order1:=xlAscending, Header:=xlGuess,
_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End if



If Range(I13).Value = Target.Value Then

Range("A1425").Select

Selection.Sort Key1:=Range("I14"), Order1:=xlAscending, Header:=xlGuess,
_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End if



If Range(J13).Value = Target.Value Then

Range("A1425").Select

Selection.Sort Key1:=Range("J14"), Order1:=xlAscending, Header:=xlGuess,
_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End if



End Sub

I would appreciate any suggestions on this little dilemma.
Thanks in advance,

A.

 

[steve]

Ashleigh,

You can replace the sort key with Columns(x), instead of Range. Than you
don't need any of the If statements. You will need to error check for "no
selection" in B12.

Dim x as Integer

x = Worksheetfunction.Match(Range("B12"),Range("A13"P13"), 0)

Selection.Sort Key1:=Columns(x), Order1:=xlAscending, _
Header:=xlGuess,OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom