I have added a custom sort order to a module I have and it is resulting in an "Run-time error '1004': Application-defined or object defined error." The only code i have added is:
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"In Progress - High,In Progress - Medium,In Progress - Low,Completed", _
DataOption:=xlSortNormal
The entire module is below.
Sub SortbyStatus()
'// sorts sheets in an order according to values housed in a F19 (Status) in each sheet
Dim arrVALUES()
Dim lngSHEETSCOUNT As Long
Dim wks As Worksheet
Dim rngCELL As Range
lngSHEETSCOUNT = ActiveWorkbook.Sheets.Count
ReDim arrVALUES(1 To lngSHEETSCOUNT, 1 To 2)
For Each wks In Worksheets
If (wks.Name <> "Overview" And wks.Name <> "List" And wks.Name <> "Template" And wks.Name <> "First") Then
arrVALUES(wks.Index, 1) = wks.Range("F19").Value
arrVALUES(wks.Index, 2) = wks.Name
Else
' Do Nothing
End If
Next wks
With Sheets.Add
With .Range("A1:B" & lngSHEETSCOUNT)
.Value = arrVALUES
.Sort key1:=.Cells(1), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"In Progress - High,In Progress - Medium,In Progress - Low,Completed", _
DataOption:=xlSortNormal
End With
On Error Resume Next
For Each rngCELL In .Range("B1:B" & lngSHEETSCOUNT)
Sheets(rngCELL.Text).Move after:=Sheets(rngCELL.Offset(-1).Text)
Next rngCELL
On Error GoTo 0
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
Sheets("Overview").Select
End Sub
Bookmarks