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!