Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
runtime error in macro
i am getting a run time error '1004' when i attempt to run this macro Code: -------------------- Sub sortandupdate1() ' ' sortandupdate1 Macro ' Macro recorded 7/14/2006 by ZACK ' Dim ws As Worksheet Set ws = ActiveSheet Dim rng As Range Set rng = ActiveSheet.Range("$A4:A" & Range("$A65536").End(xlUp).Row - 3) rng.Select Selection.sort Key1:=Range("a4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets(Array("Week 1", "Week 2", "Week 3", "Week 4", "Week 5", "Week 6", "Week 7", "Week 8", "Week 9", "Week 10", "Week 11", "Week 12", "Week 13", "Week 14", "Week 15", "Week 16", "Week 17", "Week 18", "Week 19", "Week 20", "Week 21", "Week 22", "Week 23", "Week 24")).Select Sheets(Array("Week 25", "Week 26", "Week 27", "Week 28", "Week 29", "Week 30", "Week 31", "Week 32", "Week 33", "Week 34", "Week 35", "Week 36", "Week 37", "Week 38", "Week 39", "Week 40", "Week 41", "Week 42", "Week 43", "Week 44", "Week 45", "Week 46", "Week 47", "Week 48", "Week 49")).Select Replace:=False Sheets(Array("Week 50", "Week 51", "Week 52")).Select Replace:=False ActiveWindow.SelectedSheets.FillAcrossSheets Range:=Selection, Type:= _ xlContents ws.Activate End Sub -------------------- so when i attempt to run this code it hangs up on Code: -------------------- Selection.sort Key1:=Range("a4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal -------------------- so what is wrong with my code???? i am at a loss. somebody please help me...... thanks -- papadoc ------------------------------------------------------------------------ papadoc's Profile: http://www.excelforum.com/member.php...o&userid=36463 View this thread: http://www.excelforum.com/showthread...hreadid=566884 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
runtime error in macro
Depends which version of Excel you are using, but on my XL2K, there is no
"DataOption1" argument to the .Sort method. Or "DataOption2", "DataOption3" for that matter. Also, it would be better to set the "Header" argument to something other than xlGuess; presumably you know if a header is included or not. There is no need to .Select your range or sheets in order to work with them. Set rng = ActiveSheet.Range("$A4:A" & Range("$A65536").End(xlUp).Row - 3) rng.sort Key1......etc And if these sheets represent all the WSs in the WB, you can shorten it to: ActiveWorkbook.Worksheets.FillAcrossSheets rng NickHK P.S. As shown in your code, you .Select an array of sheets. However the ..Selection object remains a range. Obviously this is how Excel is designed but seems strange to me that ..Selection does return what was last .selected. "papadoc" wrote in message ... i am getting a run time error '1004' when i attempt to run this macro Code: -------------------- Sub sortandupdate1() ' ' sortandupdate1 Macro ' Macro recorded 7/14/2006 by ZACK ' Dim ws As Worksheet Set ws = ActiveSheet Dim rng As Range Set rng = ActiveSheet.Range("$A4:A" & Range("$A65536").End(xlUp).Row - 3) rng.Select Selection.sort Key1:=Range("a4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets(Array("Week 1", "Week 2", "Week 3", "Week 4", "Week 5", "Week 6", "Week 7", "Week 8", "Week 9", "Week 10", "Week 11", "Week 12", "Week 13", "Week 14", "Week 15", "Week 16", "Week 17", "Week 18", "Week 19", "Week 20", "Week 21", "Week 22", "Week 23", "Week 24")).Select Sheets(Array("Week 25", "Week 26", "Week 27", "Week 28", "Week 29", "Week 30", "Week 31", "Week 32", "Week 33", "Week 34", "Week 35", "Week 36", "Week 37", "Week 38", "Week 39", "Week 40", "Week 41", "Week 42", "Week 43", "Week 44", "Week 45", "Week 46", "Week 47", "Week 48", "Week 49")).Select Replace:=False Sheets(Array("Week 50", "Week 51", "Week 52")).Select Replace:=False ActiveWindow.SelectedSheets.FillAcrossSheets Range:=Selection, Type:= _ xlContents ws.Activate End Sub -------------------- so when i attempt to run this code it hangs up on Code: -------------------- Selection.sort Key1:=Range("a4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal -------------------- so what is wrong with my code???? i am at a loss. somebody please help me...... thanks -- papadoc ------------------------------------------------------------------------ papadoc's Profile: http://www.excelforum.com/member.php...o&userid=36463 View this thread: http://www.excelforum.com/showthread...hreadid=566884 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
runtime error in macro
ok i pulled out of the code what you sugested and it returned the same error message..... so i don't know..... anymore thoughts -- papadoc ------------------------------------------------------------------------ papadoc's Profile: http://www.excelforum.com/member.php...o&userid=36463 View this thread: http://www.excelforum.com/showthread...hreadid=566884 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
runtime error in macro
OK, forgot the unqualified range:
Set rng = ActiveSheet.Range("$A4:A" & Range("$A65536").End(xlUp).Row - 3) should be Set rng = ActiveSheet.Range("$A4:A" & ActiveSheet.Range("$A65536").End(xlUp).Row - 3) or use the worksheets name. Also, you need to make the WS has some data in that range, otherwise the it will fail. Also the .Sort will fail. Add "Debug.print rng.Parent.name", to check that the rng is correct. NickHK "papadoc" wrote in message ... ok i pulled out of the code what you sugested and it returned the same error message..... so i don't know..... anymore thoughts -- papadoc ------------------------------------------------------------------------ papadoc's Profile: http://www.excelforum.com/member.php...o&userid=36463 View this thread: http://www.excelforum.com/showthread...hreadid=566884 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime error in Macro. | Excel Discussion (Misc queries) | |||
Macro Runtime Error 1004... help? | Excel Discussion (Misc queries) | |||
macro Runtime Error | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
naming tab macro error runtime error 1004 | Excel Programming |