Thread
:
Sorting a range on another worksheet without selecting the worksheet
View Single Post
#
2
Posted to microsoft.public.excel.programming
Dave Peterson
external usenet poster
Posts: 35,218
Sorting a range on another worksheet without selecting the worksheet
with worksheets("workorders")
.range("wo").sort _
Key1:=.Range("AB6"), Order1:=xlAscending, _
Key2:=.Range("P6"), Order2:=xlAscending, _
Key3:=.Range("O6"), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
end with
The dots in front of the ranges refer to the object in the previos with
statement--in this case worksheets("workorders")
wrote:
I have two worksheets, 'Project Rank' and 'WorkOrders'.
The named range 'pick.pn' is one cell located on 'Project Rank' and the
named range 'wo' consists of several rows located on 'WorkOrders'.
The following code works fine from any worksheet in the file:
Sub AutoSort()
Application.Goto Reference:="wo"
Selection.Sort Key1:=Range("AB6"), Order1:=xlAscending, Key2:= _
Range("P6"), Order2:=xlAscending, Key3:=Range("O6"),
Order3:=xlDescending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Application.Goto Reference:="pick.pn"
End Sub
But I shouldn't have to use the Application.Goto lines of code should
I?
Why can't the following work (from any worksheet in the file):
Sub AutoSort()
Worksheets("WorkOrders").Range("wo")
Selection.Sort Key1:=Range("AB6"), Order1:=xlAscending, Key2:= _
Range("P6"), Order2:=xlAscending, Key3:=Range("O6"),
Order3:=xlDescending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Worksheets("Project Rank").Range("pick.pn")
End Sub
I've tried and I get a Run-time error 1004: Select method of Range
class failed message.
Help appreciated.
--
Dave Peterson
Reply With Quote
Dave Peterson
View Public Profile
Find all posts by Dave Peterson