# Adding another column to automatically sort



## TJP222 (Dec 18, 2022)

Howdy,

I have this code here that automatically sorts my "Priority" column when a value is changed. I would like it to sort the priority column first and then do a secondary sort by "Due Date." I have fiddled around with it quite a bit and I can't seem to get it to work. Any help is appreciated. Thanks.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SalesTable As ListObject
Dim SortCol As Range
Set SalesTable = ActiveSheet.ListObjects("ToDo_List")
Set SortCol = Range("ToDo_List[Priority]")
If Not Intersect(Target, SortCol) Is Nothing Then
    With SalesTable.sort
        .SortFields.Clear
        .SortFields.Add Key:=SortCol, Order:=xlAscending, CustomOrder:="High,Medium,Low"
        .Header = xlYes
        .Apply
    End With
End If
End Sub


----------



## Micron (Dec 18, 2022)

Add another .SortFields.Add line and specify the column number or another range variable as the key. AFAIK, the first sort should be last in the code, the last sort should be first. 
If you post code, please use code tags (vba icon on posting toolbar) to maintain indentation and readability.


----------



## TJP222 (Dec 18, 2022)

Micron said:


> Add another .SortFields.Add line and specify the column number or another range variable as the key. AFAIK, the first sort should be last in the code, the last sort should be first.
> If you post code, please use code tags (vba icon on posting toolbar) to maintain indentation and readability.


I have tried that but I must be doing something wrong. I get "Compile Error: Type Mismatch" and it highlights "C1". Here's the code with indentions.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SalesTable As ListObject
Dim SortCol As Range
Set SalesTable = ActiveSheet.ListObjects("ToDo_List")
Set SortCol = Range("ToDo_List[Priority]")
If Not Intersect(Target, SortCol) Is Nothing Then
    With SalesTable.sort
        .SortFields.Clear
        .SortFields.Add Key:="C1", Order:=xlAscending
        .SortFields.Add Key:=SortCol, Order:=xlAscending, CustomOrder:="High,Medium,Low"
        .Header = xlYes
        .Apply
    End With
End If
End Sub


----------



## TJP222 (Dec 18, 2022)

TJP222 said:


> I have tried that but I must be doing something wrong. I get "Compile Error: Type Mismatch" and it highlights "C1". Here's the code with indentions.
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim SalesTable As ListObject
> ...




```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SalesTable As ListObject
Dim SortCol As Range
Set SalesTable = ActiveSheet.ListObjects("ToDo_List")
Set SortCol = Range("ToDo_List[Priority]")
If Not Intersect(Target, SortCol) Is Nothing Then
    With SalesTable.sort
        .SortFields.Clear
        .SortFields.Add Key:="C1", Order:=xlAscending
        .SortFields.Add Key:=SortCol, Order:=xlAscending, CustomOrder:="High,Medium,Low"
        .Header = xlYes
        .Apply
    End With
End If
End Sub
```


----------



## Micron (Dec 18, 2022)

It doesn't know what C1 is. It's expecting a range and C1 is just text. Did you try
Key:=Range("C1")? You might have to qualify that with ActiveSheet. If so, that should be safe enough as the active sheet should always be the one the code is running against since you're using Worksheet_Change (as long as you don't activate a different sheet during the event).


----------



## TJP222 (Dec 19, 2022)

Micron said:


> It doesn't know what C1 is. It's expecting a range and C1 is just text. Did you try
> Key:=Range("C1")? You might have to qualify that with ActiveSheet. If so, that should be safe enough as the active sheet should always be the one the code is running against since you're using Worksheet_Change (as long as you don't activate a different sheet during the event).


Thanks Micron


----------



## Micron (Dec 19, 2022)

Glad I could help.


----------

