Excel vba sort Run-time error ‘1004’: The sort reference is not valid. Error with second of consecutive sorts with custom sort order

27 views Asked by At

A custom sort order is being used in two consecutive sorts. The second sort always ends with the "Run-time error ‘1004’: The sort reference is not valid." This is being run in a workbook with multiple sheets. Each time the sort routine is called, rows on different sheet are being sorted. The column headers, "Project Terr." and "SpecTRAK ID" are found on both sheets. The 'FindColumnName' function returns a range for the column containing the specified header.

Here's the snippet of code which errors:

Sub TerritorySort(mysheet As Worksheet, rng As Range)
Dim vCustom_Sort As Variant
Dim n As Long
Dim rKey1 As Range, rKey2 As Range, rKey3 As Range
Dim Created As Boolean

 Created = False
 vCustom_Sort = Array("A01", "A02", "A03", "A999 N/A", "A07", "A31", "A08", "A13", "A15", "A16", _
                       "A21", "A32", "A22", "A27", "A28", "A37", "A39", "A38", "A43", "A44", Chr(42))
 n = Application.GetCustomListNum(vCustom_Sort)
 If n = 0 Then
   Application.AddCustomList ListArray:=vCustom_Sort
   Created = True
 End If
 n = Application.CustomListCount + 1
 
 Set rKey1 = FindColumnName(mysheet, "Project Terr.", 1, 0)
 Set rKey2 = FindColumnName(mysheet, "SpecTRAK ID", 1, 0)
 rng.Sort Key1:=rKey1, Order1:=xlAscending, OrderCustom:=n, _
            Key2:=rKey2, Order2:=xlAscending, Header:=xlYes

The sorts are the last section of a long script. For testing, I created a this subroutine. The routine calls the sort subroutine mentioned above. I've switched the order of the sheets being sent to the TerritorySort subroutine, but it doesn't make a difference. The first sheet sent succeeds, and the second sheet sent returns an error.

Sub TestSort()
  Dim wbAct As Workbook: Set wbAct = ActiveWorkbook
  Dim wsAct As Worksheet
  Dim DataRange As Range

  Set wsAct = wbAct.Sheets("New") 'use sheet New as active sheet
  Set DataRange = FindDataRange(wsAct)
  Call TerritorySort(wsAct, DataRange)
  wsAct.Sort.SortFields.Clear
  
  Set wsAct = wbAct.Sheets("PS-PBNQ") 'use sheet PS-PBNQ as active sheet
  Set DataRange = FindDataRange(wsAct)
  Call TerritorySort(wsAct, DataRange)
End Sub

Any help determining why will be appreciated. Thank you!

0

There are 0 answers