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.

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

"Greg Glynn" wrote in message
oups.com...
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.

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 wrote:

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

"Greg Glynn" wrote in message
oups.com...
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.


--

Dave Peterson

Dave,
Good catch, I missing that.

NickHK

"Dave Peterson" wrote in message
...
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 wrote:

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

"Greg Glynn" wrote in message
oups.com...
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.


--

Dave Peterson