[SOLVED] 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

"Greg Glynn" <GregoryGlynn@gmail.com> wrote in message
news:1153282561.650961.319510@b28g2000cwb.googlegroups.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]

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" <GregoryGlynn@gmail.com> wrote in message
> news:1153282561.650961.319510@b28g2000cwb.googlegroups.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

[NickHK]

Dave,
Good catch, I missing that.

NickHK

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:44BE1EF5.73EC020C@verizonXSPAM.net...
> 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" <GregoryGlynn@gmail.com> wrote in message
> > news:1153282561.650961.319510@b28g2000cwb.googlegroups.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