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