Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro not working properly when run automatically
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro is not working properly | Excel Discussion (Misc queries) | |||
my sum function is not working properly | Excel Discussion (Misc queries) | |||
But not working properly | Excel Discussion (Misc queries) | |||
Macro not working properly when the file is shared | Excel Worksheet Functions | |||
Macro "1 tall/1 wide" not working properly | Excel Worksheet Functions |