Need Help with a sort

[Greg Glynn]

Can anyone see why this code produces an error?

Windows(ImportFileName).Activate
ActiveWorkbook.ActiveSheet.Cells.Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending,
Key2:=Range("C1") _
, Order2:=xlAscending, Key3:=Range("D1"), Order3:=xlAscending,
Header:=xlNo, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom


The error is:
Run-time Error 1004:
The Sort reference is not valid.

... it's driving me batty.

Thanks in advance.

 

[NickHK]

It is seldom necessary or desirable to select object in Excel. This works,
as long as the WB and WS names are valid:

Application.Workbooks(ImportFileName).Worksheets("Sort").Cells.Sort _
Key1:=Range("B1"), Order1:=xlAscending, _
Key2:=Range("C1"), Order2:=xlAscending, _
Key3:=Range("D1"), Order3:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

It may be better use .UsedRange or .CurrentRegion for the range to sort,
depending on layout of you sheet.

NickHK

 

[Dave Peterson]

Just a warning that that worksheet Sort will have to be active (since the keys
are unqualified).

Maybe...

with Workbooks(ImportFileName).Worksheets("Sort")
.Cells.Sort Key1:=.Range("B1"), Order1:=xlAscending, _
Key2:=.Range("C1"), Order2:=xlAscending, _
Key3:=.Range("D1"), Order3:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
end with

To the OP, those dots (.range("b1")) mean that the ranges belong to the object
in the previous With statement.

 

[NickHK]

Dave,
Good catch, I missing that.

NickHK

Just a warning that that worksheet Sort will have to be active (since the keys
are unqualified).

Maybe...

with Workbooks(ImportFileName).Worksheets("Sort")
.Cells.Sort Key1:=.Range("B1"), Order1:=xlAscending, _
Key2:=.Range("C1"), Order2:=xlAscending, _
Key3:=.Range("D1"), Order3:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
end with

To the OP, those dots (.range("b1")) mean that the ranges belong to the object
in the previous With statement.