Thankyou for your time, but I had one of those wonderful "Ooh, I wonder..."
moments last night and it appears to be working now.

I separated my one large macro into three smaller ones (convert to numbers,
sort, convert to letters) then made a macro that just ran those three. I then
set that macro to run when the wookbook was saved (as I had tried to do
before) and it worked properly.

So again, thanks for your time anyway.

"Dave Peterson" wrote:

You haven't shared many details, so I used:

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

You'll want to change the name of the sheet with the list from Hidden to what
you use.

I used A1 through the last used cell in column A. You'll have to modify that to
use the range you want.

And the sort statement should be what you need, too.

If this doesn't help, then post back with your modified code and more details.

ATChurch wrote:

No, I didn't, as when I looked for it I couldn't find it (/blush). "Notify me
of replies" doesn't seem to be working for me either. Since I put more detail
into this one, I'll continue here (from your earlier post)

Thanks for the code, although I have no programming training at all, I can
be Ok at picking out what parts do what if I know what I'm looking at, but
this has me at a total loss. I just don't know where to begin editing it for
my use.

"Dave Peterson" wrote:

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

"Dave Peterson" wrote:

Did you read the replies to yesterday's post?

ATChurch wrote:

Hi there, I thought I posted a thread about this, but can't seem to find it.

Anyway, the problem is I have the macro below. It works when I run it
manually from the macro menu, but bugs when I use VB to edit it into running
automaticaly (tried Wookbook_Open and Wookbook_BeforeSave). Using Ex2003 by
the way.

It's supposed to sort all sheets according to High, Medium, Low by:
1-Convert High, Medium, Low to 1,2,3 accross workbook.
2-Sort each sheet according to 1,2,3
3-Convert 1,2,3 back into High, Medium, Low.

Now when I run it automatically, if there is High, Medium, Low but not yet
sorted, then it will convert it into 1,2,3 then stop. If High, Medium, Low is
already present and sorted, then it will skip converting it and just resort
it as High, Low, Medium.

I've previously tried a custom list to do this, but those seem to be stored
locally, not in the actual wookbook, which means its not an ideal as I need
to pass this around collegues who may not always use the same PC.
Additionally, they're not the most computer literate, so I don't want
anything more complicated than "Openenter datasaveclose" for them.

The macro is:
Cells.Replace What:="High", Replacement:="1", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Medium", Replacement:="2", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Low", Replacement:="3", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Andrew Welsh").Select
Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _
"E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Sheets("Mark Watkins").Select
Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _
"E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Sheets("Steve Lane").Select
Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _
"E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Cells.Replace What:="3", Replacement:="Low", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="2", Replacement:="Medium", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="1", Replacement:="High", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

--

Dave Peterson
.


--

Dave Peterson
.