VBA Sort causes Excel to crash

[TH_AjeTs]

Sub AutoSort2()
'
' AutoSort Macro
' This will sort by the list of topics
'
    Dim dynlist As Variant
    dynlist = Application.Transpose(Sheet2.ListObjects("table2").ListColumns(2).DataBodyRange)
   
    ActiveTable = ActiveSheet.Range("b15").ListObject.Name
    ActiveSheet.ListObjects(ActiveTable).Sort.SortFields.Clear
    Application.AddCustomList listarray:=dynlist
    ActiveSheet.ListObjects(ActiveTable).Sort.SortFields. _
        Add2 Key:=ActiveSheet.ListObjects(ActiveTable).ListColumns(7).DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, _
        CustomOrder:=Application.CustomListCount, _
        DataOption:=xlSortNormal
    With ActiveSheet.ListObjects(ActiveTable).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.DeleteCustomList Application.CustomListCount
   
   ActiveSheet.Calculate
   Sheet2.Calculate
   Sheet5.Calculate
   Sheet6.Calculate

End Sub

First off, I'm seriously noob at VBA, I just google stuff and put various solutions together to achieve what I want.

I just got off microsoft support which help pin-point that the VBA is the issue. Because when I try to manually sort, nothing goes wrong. But if I should use the macro, then try to save afterwards, Excel would crash.

Somebody save me pls. Thanks

 

[RoryA]

Welcome to the forum!

Try adding:

ActiveSheet.ListObjects(ActiveTable).Sort.SortFields.Clear

before the line that deletes the custom list you created.

 

[TH_AjeTs]

Thanks everyone. I think I finally figured it out. Here's the modified code:

Sub AutoSort2()
'
' AutoSort Macro
' This will sort by the list of topics
'
Dim dynlist As Variant
Dim ActiveTable2 As Variant

Let dynlist = Application.Transpose(Sheet2.ListObjects("table2").ListColumns(2).DataBodyRange)
Let ActiveTable2 = ActiveSheet.Range("b15").ListObject.Name

ActiveSheet.ListObjects(ActiveTable2).Sort.SortFields.Clear
Application.AddCustomList listarray:=dynlist
ActiveSheet.ListObjects(ActiveTable2).Sort.SortFields. _
Add2 Key:=ActiveSheet.ListObjects(ActiveTable2).ListColumns(7).DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:=Application.CustomListCount, _
DataOption:=xlSortNormal
With ActiveSheet.ListObjects(ActiveTable2).Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.ListObjects(ActiveTable2).Sort.SortFields.Clear

ActiveSheet.Calculate
Sheet2.Calculate
Sheet5.Calculate
Sheet6.Calculate

Application.DeleteCustomList listNum:=Application.CustomListCount

End Sub

I narrowed it down to the application.deletecustomlist and after some googling, I found out that just specifying with listNum:=5 worked.
So in summary, I modified
Application.DeleteCustomList Application.CustomListCount
to
Application.DeleteCustomList listNum:=Application.CustomListCount

 

[TH_AjeTs]

Welcome to the forum!

Try adding:

ActiveSheet.ListObjects(ActiveTable).Sort.SortFields.Clear

before the line that deletes the custom list you created.

This suggestion was also necessary to the final solution. Thanks so much!