Custom Sort order issue

  • Thread starter Thread starter ATChurch
  • Start date Start date
A

ATChurch

Hi. I have created a custom sort order by using a list in a hidden sheet in a
workbook. This is then followed by two default sorts.

I recorded a macro of this sort being performed on each of the sheets in the
work book and then used the Visual Basic edittor to make it run each time the
workbook is opened.

The problem is, this works perfectly for me, but when I emailed it to my
collegues who will also use it, the custom sort had reverted to a default
(alphabetical) sort.

Further, when they email me back the edited table, the macro works fine when
I open it.

What's going on?

PS: The custom sort is for a table containing a "High/Medium/Low" rating
column and it is instead sorting to "High/Low/Medium".
 
Maybe you could add the customlist in code, do the sort, and remove the
customlist.

Maybe you can add the customlist, use it and delete it all in code:

Option Explicit
Sub testme01()

Dim myArr As Variant
Dim myListNumber As Long
Dim myRng As Range

With Worksheets("Hidden")
myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With

'or lose the hidden sheet and do it all in code???
'myArr = Array("Grape", "Apple", "Orange", "Banana", "Melon")

Application.AddCustomList listarray:=myArr
myListNumber = Application.GetCustomListNum(myArr)

Set myRng = Worksheets("Sheet999").Range("a1:k30")

With myRng
.Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=myListNumber + 1
End With

Application.DeleteCustomList myListNumber

End Sub
 
Back
Top