runtime error in macro

[papadoc]

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

 

[NickHK]

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.

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

 

[papadoc]

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

 

[NickHK]

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