Sorting a range on another worksheet without selecting the worksheet

  • Thread starter Thread starter Scott.Prestridge
  • Start date Start date
S

Scott.Prestridge

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.
 
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")
 
Back
Top