Xl crashing out of CustomSort - consistent

[scoha]

I am getting a consistent crash in XL and its got something to do with the CustomSort code I have included, as follows:

Private Sub Worksheet_Activate()
Application.EnableEvents = False

Dim i As Long, LR As Long, x As Long

'This bit searches through OpenIssues list looking for Closed issues with Date Resolved
'Then transfers this through to Closed Issues and deletes from Open Issues

With Sheets("OpenIssues")
   
        LR = .Range("B" & Rows.Count).End(xlUp).Row
    
    For i = LR To 7 Step -1

    If .Cells(i, "D") = "Closed" And .Cells(i, "L") > 0 Then
        .Rows(i).Copy
        Sheets("ClosedIssues").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        .Rows(i).EntireRow.Delete
    End If
Next i
End With

'This bit sorts the OpenIssues according to CustomSort List

    'Deletes any Custom Lists already installed
        For x = Application.CustomListCount To 5 Step -1
            Application.DeleteCustomList (x)
        Next x
        
        With Sheets("Validations")
            .Range("g8") = Application.CustomListCount
            iCustomListNum = Application.CustomListCount + 2
            .Range("g10") = iCustomListNum
            Application.AddCustomList .Range("myCustomSort")
        End With
    
'    ' custom sort order text
'    Application.AddCustomList ListArray:=Sheets("Validations").Range("myCustomSort")

    ' sort using latest custom sort order entry
    Range("a7:l" & LR).Sort Key1:=Range("G6"), Order1:=xlAscending, Header:=xlGuess, _
                OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
                Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

    ' remove latest custom sort entry order
    Application.DeleteCustomList Application.CustomListCount

Application.EnableEvents = True
End Sub

Within "Validations" I store the CustomListCount and iCustomListNum and these get populated during the macro run. Also, the sort is successful and works the way ot is expected.

Just when I save the file it crashes each time - no xl error message - just the Windows Excel has crashed message,

Can anyone find fault with the code or make a suggestion?

 

[mikerickson]

perhaps OrderCustom:= CustomListCount - 1
or perhaps +0.

 

[scoha]

Thanks Mickerickson,

tried that but the only way the sort worked was with OrderCustom:=Application.CustomListCount + 1,

but then Excel crashes when I save - although the macro appears to execute correctly.

This ones driving me balmy.

Ive even tried transferring all the code to a new workbook but still same result

 

[mikerickson]

Is it possible that the custom list is illegal in some way. Too long perhaps.

 

[scoha]

No - its only three cells long.

Was wondering if the sort code couldnt work out which sheet to use so have prefaced with a Sheets("OpenIssues").Activate but still crashes Excel on save. The sort seems to work but I cant save.

Is this a XL2010 issue??

 

[mikerickson]

I don't know about 2010, but in a multi-book environment, one should fully qualify all references.