I feel very foolish asking this question but I have been going round and
round on this too long. I have the following code in a Userform (not in a
module):
Sheets(1).Activate
Sheets(1).Range(Cells(3,1),Cells(15,20)).Select
Selection.sort Key1:=Range(€œA2€�), Order1:=xlAscending _
, Key2:=Range(€œD2€�), Order2:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False _
, Orientation:xlTopToBottom
The code works fine.
However, I now want to do the sort without activating sheet(1). I have
tried all kinds of ways to do the ranges without using selection but to no
avail. I went thru the postings and could not find another posting close to
this. Can someone help me.

--
russ

Try this... I have changed Header:=xlGuess to xlYes instead of letting XL
make a guess on your behalf...

Sheets(1).Range("A3:T15").Sort Key1:=Range("A2"), _
Order1:=xlAscending, _
Key2:=Range("D2"), _
Order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False
--
HTH...

Jim Thomlinson


"Russ" wrote:

I feel very foolish asking this question but I have been going round and
round on this too long. I have the following code in a Userform (not in a
module):
Sheets(1).Activate
Sheets(1).Range(Cells(3,1),Cells(15,20)).Select
Selection.sort Key1:=Range(€œA2€�), Order1:=xlAscending _
, Key2:=Range(€œD2€�), Order2:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False _
, Orientation:xlTopToBottom
The code works fine.
However, I now want to do the sort without activating sheet(1). I have
tried all kinds of ways to do the ranges without using selection but to no
avail. I went thru the postings and could not find another posting close to
this. Can someone help me.

--
russ

with sheets(1)
with .range(.cells(3,1),.cells(15,20))
.cells.sort Key1:=.columns(1), Order1:=xlAscending, _
Key2:=.columns(3), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:xlTopToBottom
end with
end with

Notice all the dots (like .cells()). That means that that thing belongs to the
object in the previous With statement.

And I agree with Jim's tip about not letting xl guess (about headers). You know
if you have them or not. Put that in your code (xlyes or xlno).

And sheets(1) will be the left most sheet. I'd use the name:
with sheets("mysheetnamehere")
if I knew it.


Russ wrote:

I feel very foolish asking this question but I have been going round and
round on this too long. I have the following code in a Userform (not in a
module):
Sheets(1).Activate
Sheets(1).Range(Cells(3,1),Cells(15,20)).Select
Selection.sort Key1:=Range(€œA2€�), Order1:=xlAscending _
, Key2:=Range(€œD2€�), Order2:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False _
, Orientation:xlTopToBottom
The code works fine.
However, I now want to do the sort without activating sheet(1). I have
tried all kinds of ways to do the ranges without using selection but to no
avail. I went thru the postings and could not find another posting close to
this. Can someone help me.

--
russ

--

Dave Peterson

Oh Dave you don't know how long I worked on this one. I can't thank you
enough. As an aside - I do always use sheets("mysheet") but for illustration
purposes i used sheets(1) in my sample code. Where I got all tangled up was
trying to find the alternative for key1:=Range("A2") - i had not tried
key1:=.columns(1).
What would amateurs like me do without you guys. Thanks again.

--
russ


"Dave Peterson" wrote:

with sheets(1)
with .range(.cells(3,1),.cells(15,20))
.cells.sort Key1:=.columns(1), Order1:=xlAscending, _
Key2:=.columns(3), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:xlTopToBottom
end with
end with

Notice all the dots (like .cells()). That means that that thing belongs to the
object in the previous With statement.

And I agree with Jim's tip about not letting xl guess (about headers). You know
if you have them or not. Put that in your code (xlyes or xlno).

And sheets(1) will be the left most sheet. I'd use the name:
with sheets("mysheetnamehere")
if I knew it.


Russ wrote:

I feel very foolish asking this question but I have been going round and
round on this too long. I have the following code in a Userform (not in a
module):
Sheets(1).Activate
Sheets(1).Range(Cells(3,1),Cells(15,20)).Select
Selection.sort Key1:=Range(€œA2€�), Order1:=xlAscending _
, Key2:=Range(€œD2€�), Order2:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False _
, Orientation:xlTopToBottom
The code works fine.
However, I now want to do the sort without activating sheet(1). I have
tried all kinds of ways to do the ranges without using selection but to no
avail. I went thru the postings and could not find another posting close to
this. Can someone help me.

--
russ

--

Dave Peterson

Thanks Jim - i incorporated your suggestion in the code that Dave Peterson
posted and the combination worked just great. Many thanks.
--
russ


"Jim Thomlinson" wrote:

Try this... I have changed Header:=xlGuess to xlYes instead of letting XL
make a guess on your behalf...

Sheets(1).Range("A3:T15").Sort Key1:=Range("A2"), _
Order1:=xlAscending, _
Key2:=Range("D2"), _
Order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False
--
HTH...

Jim Thomlinson


"Russ" wrote:

I feel very foolish asking this question but I have been going round and
round on this too long. I have the following code in a Userform (not in a
module):
Sheets(1).Activate
Sheets(1).Range(Cells(3,1),Cells(15,20)).Select
Selection.sort Key1:=Range(€œA2€�), Order1:=xlAscending _
, Key2:=Range(€œD2€�), Order2:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False _
, Orientation:xlTopToBottom
The code works fine.
However, I now want to do the sort without activating sheet(1). I have
tried all kinds of ways to do the ranges without using selection but to no
avail. I went thru the postings and could not find another posting close to
this. Can someone help me.

--
russ

Depending on the range you were sorting, you could have used:

..range("a2")
or
sameworksheet.range("a2")

but (for me), it makes it easier to see that I'm sorting on the 2nd column of
the range to sort.



Russ wrote:

Oh Dave you don't know how long I worked on this one. I can't thank you
enough. As an aside - I do always use sheets("mysheet") but for illustration
purposes i used sheets(1) in my sample code. Where I got all tangled up was
trying to find the alternative for key1:=Range("A2") - i had not tried
key1:=.columns(1).
What would amateurs like me do without you guys. Thanks again.

--
russ

"Dave Peterson" wrote:

with sheets(1)
with .range(.cells(3,1),.cells(15,20))
.cells.sort Key1:=.columns(1), Order1:=xlAscending, _
Key2:=.columns(3), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:xlTopToBottom
end with
end with

Notice all the dots (like .cells()). That means that that thing belongs to the
object in the previous With statement.

And I agree with Jim's tip about not letting xl guess (about headers). You know
if you have them or not. Put that in your code (xlyes or xlno).

And sheets(1) will be the left most sheet. I'd use the name:
with sheets("mysheetnamehere")
if I knew it.


Russ wrote:

I feel very foolish asking this question but I have been going round and
round on this too long. I have the following code in a Userform (not in a
module):
Sheets(1).Activate
Sheets(1).Range(Cells(3,1),Cells(15,20)).Select
Selection.sort Key1:=Range(€œA2€�), Order1:=xlAscending _
, Key2:=Range(€œD2€�), Order2:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False _
, Orientation:xlTopToBottom
The code works fine.
However, I now want to do the sort without activating sheet(1). I have
tried all kinds of ways to do the ranges without using selection but to no
avail. I went thru the postings and could not find another posting close to
this. Can someone help me.

--
russ

--

Dave Peterson


--

Dave Peterson

I agree. The .columns(n) makes it easier. I think the dots not in the right
places was also giving me grief. I thought i had that finally figured out.
Thanks again for your help.
russ


"Dave Peterson" wrote:

Depending on the range you were sorting, you could have used:

..range("a2")
or
sameworksheet.range("a2")

but (for me), it makes it easier to see that I'm sorting on the 2nd column of
the range to sort.



Russ wrote:

Oh Dave you don't know how long I worked on this one. I can't thank you
enough. As an aside - I do always use sheets("mysheet") but for illustration
purposes i used sheets(1) in my sample code. Where I got all tangled up was
trying to find the alternative for key1:=Range("A2") - i had not tried
key1:=.columns(1).
What would amateurs like me do without you guys. Thanks again.

--
russ

"Dave Peterson" wrote:

with sheets(1)
with .range(.cells(3,1),.cells(15,20))
.cells.sort Key1:=.columns(1), Order1:=xlAscending, _
Key2:=.columns(3), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:xlTopToBottom
end with
end with

Notice all the dots (like .cells()). That means that that thing belongs to the
object in the previous With statement.

And I agree with Jim's tip about not letting xl guess (about headers). You know
if you have them or not. Put that in your code (xlyes or xlno).

And sheets(1) will be the left most sheet. I'd use the name:
with sheets("mysheetnamehere")
if I knew it.


Russ wrote:

I feel very foolish asking this question but I have been going round and
round on this too long. I have the following code in a Userform (not in a
module):
Sheets(1).Activate
Sheets(1).Range(Cells(3,1),Cells(15,20)).Select
Selection.sort Key1:=Range(€ŀœA2€ÂÂ�), Order1:=xlAscending _
, Key2:=Range(€ŀœD2€ÂÂ�), Order2:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False _
, Orientation:xlTopToBottom
The code works fine.
However, I now want to do the sort without activating sheet(1). I have
tried all kinds of ways to do the ranges without using selection but to no
avail. I went thru the postings and could not find another posting close to
this. Can someone help me.

--
russ

--

Dave Peterson


--

Dave Peterson