View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
NickHK NickHK is offline
external usenet poster
 
Posts: 4,391
Default Need Help with a sort

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.