runtime error in macro

i am getting a run time error '1004' when i attempt to run this macro

Please Login or Register  to view this content.

so when i attempt to run this code it hangs up on

Please Login or Register  to view this content.

so what is wrong with my code???? i am at a loss.

somebody please help me......
thanks

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

ok i pulled out of the code what you sugested and it returned the same error message..... so i don't know..... anymore thoughts

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>