Macro to Automatically Sort Multiple Column Groups Separately As Data is Entered

[baaronwx]

I have searched extensively, but have not found a solution to fit my specific need so it is time to ask for help. I have a worksheet that contains multiple task lists, each having two columns, a "Priority" field and a "Description" field. The data should be sorted by Priority first and by Description second. The header row is 5 and the data is in rows 6 through 50. The first list is in A5:B50, the second C5:D50, and so on until the sixth list in K5:L50. I have a macro that works for one task list, but cannot get it to function for multiple task lists. After sinking hours into variations, I have now surrendered to my time constraints to get the help of the masters here. Below is the macro that functions for the first task list. It is in the code for the sheet tab.

Option Explicit
Private Sub Worksheet_Change(ByVal Target1 As Range)
If Target1.Column = 1 Then
Range("A5:B50").Sort _
Key1:=Range("A5"), Order1:=xlAscending, _
Key2:=Range("B5"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If


End Sub

 

[baaronwx]

After much research and trial and error, I have written the macro below to automatically sort by two keys, six separate column groups on the same sheet. I would appreciate any constructive criticism from the experts for increasing simplicity or efficiency.


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Range("A5:B50").Sort _
Key1:=Range("A5"), Order1:=xlAscending, _
Key2:=Range("B5"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
ElseIf Target.Column = 3 Then
Range("C5:D50").Sort _
Key1:=Range("C5"), Order1:=xlAscending, _
Key2:=Range("D5"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
ElseIf Target.Column = 5 Then
Range("E5:F50").Sort _
Key1:=Range("E5"), Order1:=xlAscending, _
Key2:=Range("F5"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
ElseIf Target.Column = 7 Then
Range("G5:H50").Sort _
Key1:=Range("G5"), Order1:=xlAscending, _
Key2:=Range("H5"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
ElseIf Target.Column = 9 Then
Range("I5:J50").Sort _
Key1:=Range("I5"), Order1:=xlAscending, _
Key2:=Range("J5"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
ElseIf Target.Column = 11 Then
Range("K5:L50").Sort _
Key1:=Range("K5"), Order1:=xlAscending, _
Key2:=Range("L5"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub