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

baaronwx

New Member
Joined
Apr 16, 2014
Messages
6
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

New Member
Joined
Apr 16, 2014
Messages
6
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
 

Forum statistics

Threads
1,082,548
Messages
5,366,227
Members
400,880
Latest member
dwb

Some videos you may like

This Week's Hot Topics

Top