Hello All,
In the General Module I have:
Sub proSortByName()
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Clear
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Add Key:=Range
("B10:B80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Add Key:=Range
("A10:A80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Plyrs").Sort
.SetRange Range("A10:C80")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
Sheet2.Range("A3").Select
End Sub

When I select sheet2 the code runs but it is still indicating that
Sheet1 "Plyrs" is selected, how do i get it deselect.
Thks in adv and Rgds
KZ

I'd make sure that all the ranges are qualified.

Option Explicit
Sub proSortByName()
with ActiveWorkbook.Worksheets("Plyrs")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("B10:B80"), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal

.Sort.SortFields.Add Key:=.Range("A10:A80"), _
sortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal

.sort.SetRange .Range("A10:C80")

with .sort
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
end with
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
me.Range("A3").Select
End Sub

(Untested, uncompiled. Watch for typos.)

Kieranz wrote:

Hello All,
In the General Module I have:
Sub proSortByName()
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Clear
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Add Key:=Range
("B10:B80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Add Key:=Range
("A10:A80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Plyrs").Sort
.SetRange Range("A10:C80")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
Sheet2.Range("A3").Select
End Sub

When I select sheet2 the code runs but it is still indicating that
Sheet1 "Plyrs" is selected, how do i get it deselect.
Thks in adv and Rgds
KZ

--

Dave Peterson

On Mar 3, 7:12*pm, Dave Peterson wrote:
I'd make sure that all the ranges are qualified.

Option Explicit
Sub proSortByName()
* *with ActiveWorkbook.Worksheets("Plyrs")
* * * *.Sort.SortFields.Clear
* * * *.Sort.SortFields.Add Key:=.Range("B10:B80"), _
* * * * * *SortOn:=xlSortOnValues, Order:=xlAscending, _
* * * * * *DataOption:=xlSortNormal

* * * *.Sort.SortFields.Add Key:=.Range("A10:A80"), _
* * * * * *sortOn:=xlSortOnValues, Order:=xlAscending, _
* * * * * *DataOption:=xlSortNormal

* * * *.sort.SetRange .Range("A10:C80")

* * * *with .sort
* * * * *.Header = xlGuess
* * * * *.MatchCase = False
* * * * *.Orientation = xlTopToBottom
* * * * *.SortMethod = xlPinYin
* * * * *.Apply
* * * *End With
* *end with
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
* Call proSortByName
* me.Range("A3").Select
End Sub

(Untested, uncompiled. *Watch for typos.)



Kieranz wrote:

Hello All,
In the General Module I have:
Sub proSortByName()
* *ActiveWorkbook.Worksheets("Plyrs").Sort.SortField s.Clear
* *ActiveWorkbook.Worksheets("Plyrs").Sort.SortField s.Add Key:=Range
("B10:B80") _
* * * *, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
* *ActiveWorkbook.Worksheets("Plyrs").Sort.SortField s.Add Key:=Range
("A10:A80") _
* * * *, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
* *With ActiveWorkbook.Worksheets("Plyrs").Sort
* * * *.SetRange Range("A10:C80")
* * * *.Header = xlGuess
* * * *.MatchCase = False
* * * *.Orientation = xlTopToBottom
* * * *.SortMethod = xlPinYin
* * * *.Apply
* *End With
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
Sheet2.Range("A3").Select
End Sub

When I select sheet2 the code runs but it is still indicating that
Sheet1 "Plyrs" is selected, how do i get it deselect.
Thks in adv and Rgds
KZ

--

Dave Peterson

Hi Dave, Many thks I tried but when I select sheet2 the code runs but
it is indicating that the range (A10:C80) on Sheet1 "Plyrs" is
selected, how do i get it deselect.
So that when i am on Sheet2 it not smudging or overlapping a portion
of the set range. I am still on dial up this part of the world so your
patience appreciated.
Look forward to your expert knowhow. Take care Rgds
KZ

What's your current code look like?

What line causes the trouble?

What's the name of the worksheet that contains the event procedure?

wrote:

On Mar 3, 7:12 pm, Dave Peterson wrote:
I'd make sure that all the ranges are qualified.

Option Explicit
Sub proSortByName()
with ActiveWorkbook.Worksheets("Plyrs")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("B10:B80"), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal

.Sort.SortFields.Add Key:=.Range("A10:A80"), _
sortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal

.sort.SetRange .Range("A10:C80")

with .sort
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
end with
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
me.Range("A3").Select
End Sub

(Untested, uncompiled. Watch for typos.)



Kieranz wrote:

Hello All,
In the General Module I have:
Sub proSortByName()
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Clear
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Add Key:=Range
("B10:B80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Add Key:=Range
("A10:A80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Plyrs").Sort
.SetRange Range("A10:C80")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
Sheet2.Range("A3").Select
End Sub

When I select sheet2 the code runs but it is still indicating that
Sheet1 "Plyrs" is selected, how do i get it deselect.
Thks in adv and Rgds
KZ

--

Dave Peterson

Hi Dave, Many thks I tried but when I select sheet2 the code runs but
it is indicating that the range (A10:C80) on Sheet1 "Plyrs" is
selected, how do i get it deselect.
So that when i am on Sheet2 it not smudging or overlapping a portion
of the set range. I am still on dial up this part of the world so your
patience appreciated.
Look forward to your expert knowhow. Take care Rgds
KZ

--

Dave Peterson

On Mar 4, 6:20*pm, Dave Peterson wrote:
What's your current code look like?

What line causes the trouble?

What's the name of the worksheet that contains the event procedure?



wrote:

On Mar 3, 7:12 pm, Dave Peterson wrote:
I'd make sure that all the ranges are qualified.

Option Explicit
Sub proSortByName()
* *with ActiveWorkbook.Worksheets("Plyrs")
* * * *.Sort.SortFields.Clear
* * * *.Sort.SortFields.Add Key:=.Range("B10:B80"), _
* * * * * *SortOn:=xlSortOnValues, Order:=xlAscending, _
* * * * * *DataOption:=xlSortNormal

* * * *.Sort.SortFields.Add Key:=.Range("A10:A80"), _
* * * * * *sortOn:=xlSortOnValues, Order:=xlAscending, _
* * * * * *DataOption:=xlSortNormal

* * * *.sort.SetRange .Range("A10:C80")

* * * *with .sort
* * * * *.Header = xlGuess
* * * * *.MatchCase = False
* * * * *.Orientation = xlTopToBottom
* * * * *.SortMethod = xlPinYin
* * * * *.Apply
* * * *End With
* *end with
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
* Call proSortByName
* me.Range("A3").Select
End Sub

(Untested, uncompiled. *Watch for typos.)

Kieranz wrote:

Hello All,
In the General Module I have:
Sub proSortByName()
* *ActiveWorkbook.Worksheets("Plyrs").Sort.SortField s.Clear
* *ActiveWorkbook.Worksheets("Plyrs").Sort.SortField s.Add Key:=Range
("B10:B80") _
* * * *, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
* *ActiveWorkbook.Worksheets("Plyrs").Sort.SortField s.Add Key:=Range
("A10:A80") _
* * * *, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
* *With ActiveWorkbook.Worksheets("Plyrs").Sort
* * * *.SetRange Range("A10:C80")
* * * *.Header = xlGuess
* * * *.MatchCase = False
* * * *.Orientation = xlTopToBottom
* * * *.SortMethod = xlPinYin
* * * *.Apply
* *End With
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
Sheet2.Range("A3").Select
End Sub

When I select sheet2 the code runs but it is still indicating that
Sheet1 "Plyrs" is selected, how do i get it deselect.
Thks in adv and Rgds
KZ

--

Dave Peterson

Hi Dave, Many thks I tried but when I select sheet2 the code runs but
it is indicating that the range (A10:C80) on Sheet1 "Plyrs" is
selected, how do i get it deselect.
So that when i am on Sheet2 it not smudging or overlapping a portion
of the set range. I am still on dial up this part of the world so your
patience appreciated.
Look forward to your expert knowhow. Take care Rgds
KZ

--

Dave Peterson

Dave, thks for your response. Scenerio is: Sheet 1 is "Plyrs" Sheet 2
is "Draw". "Plyrs" has Col A Lastname Col B Firstname and Col C Town.
I then have Col D Fullname which is concatenation of Firstname &
Lastname & Town. As players (about 180 - 200) report we record their
details in the Plyrs sheet. Latter we are required to come up with a
Draw Sch according to fixed times and group the players in pairs.
(similar to golf if u r a golfer :) ). So I have defined Col D in
"Plyrs" as LIST name range. In the Draw sheet I use the LIST via the
validation dropdown arrow to pick the player. To make the player
selection easy in the Draw sheet the LIST is to be in alpha order. So
to the Draw sheet i have attached the Worksheet_activate sub which
then calls the SortByName procedure in the general module.

The idea being that everytime i click the Draw sheet the LIST will be
in alpha order. I hope u follow the drift Dave, sorry about my english
grammer.

Issue: the codes are executing okay no errors. But when I am in the
Draw sheet a portion of the "SetRange Range("A10:C80") is showing thru
(I suspect because of my other fancy formattings); ie its like an
overlay. And when I go to the Plyrs sheet the SetRange is highlighted
as selected. How do I get the code to deselect the SetRange? Or is
there a better way to recode the whole thing?
Much obliged and gratified for your and other experts like u who take
time and effort to help us, particularly for me coming from Zambia
Africa where access to both people and internet is very hard. Thks
again. Rgds KZ

It's very rare that you have to select anything to work with it. And if you
don't select stuff, the code becomes easier to read/maintain and debug.

I wouldn't rely on selecting a range for the stuff you're doing.

But I really don't have a guess why the sort code is failing. Maybe you can
post your current code and indicate any line that is causing errors.

wrote:

On Mar 4, 6:20 pm, Dave Peterson wrote:
What's your current code look like?

What line causes the trouble?

What's the name of the worksheet that contains the event procedure?



wrote:

On Mar 3, 7:12 pm, Dave Peterson wrote:
I'd make sure that all the ranges are qualified.

Option Explicit
Sub proSortByName()
with ActiveWorkbook.Worksheets("Plyrs")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("B10:B80"), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal

.Sort.SortFields.Add Key:=.Range("A10:A80"), _
sortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal

.sort.SetRange .Range("A10:C80")

with .sort
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
end with
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
me.Range("A3").Select
End Sub

(Untested, uncompiled. Watch for typos.)

Kieranz wrote:

Hello All,
In the General Module I have:
Sub proSortByName()
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Clear
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Add Key:=Range
("B10:B80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Plyrs").Sort.SortFields .Add Key:=Range
("A10:A80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Plyrs").Sort
.SetRange Range("A10:C80")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

In Sheet2 the code:
Private Sub Worksheet_Activate()
Call proSortByName
Sheet2.Range("A3").Select
End Sub

When I select sheet2 the code runs but it is still indicating that
Sheet1 "Plyrs" is selected, how do i get it deselect.
Thks in adv and Rgds
KZ

--

Dave Peterson

Hi Dave, Many thks I tried but when I select sheet2 the code runs but
it is indicating that the range (A10:C80) on Sheet1 "Plyrs" is
selected, how do i get it deselect.
So that when i am on Sheet2 it not smudging or overlapping a portion
of the set range. I am still on dial up this part of the world so your
patience appreciated.
Look forward to your expert knowhow. Take care Rgds
KZ

--

Dave Peterson

Dave, thks for your response. Scenerio is: Sheet 1 is "Plyrs" Sheet 2
is "Draw". "Plyrs" has Col A Lastname Col B Firstname and Col C Town.
I then have Col D Fullname which is concatenation of Firstname &
Lastname & Town. As players (about 180 - 200) report we record their
details in the Plyrs sheet. Latter we are required to come up with a
Draw Sch according to fixed times and group the players in pairs.
(similar to golf if u r a golfer :) ). So I have defined Col D in
"Plyrs" as LIST name range. In the Draw sheet I use the LIST via the
validation dropdown arrow to pick the player. To make the player
selection easy in the Draw sheet the LIST is to be in alpha order. So
to the Draw sheet i have attached the Worksheet_activate sub which
then calls the SortByName procedure in the general module.

The idea being that everytime i click the Draw sheet the LIST will be
in alpha order. I hope u follow the drift Dave, sorry about my english
grammer.

Issue: the codes are executing okay no errors. But when I am in the
Draw sheet a portion of the "SetRange Range("A10:C80") is showing thru
(I suspect because of my other fancy formattings); ie its like an
overlay. And when I go to the Plyrs sheet the SetRange is highlighted
as selected. How do I get the code to deselect the SetRange? Or is
there a better way to recode the whole thing?
Much obliged and gratified for your and other experts like u who take
time and effort to help us, particularly for me coming from Zambia
Africa where access to both people and internet is very hard. Thks
again. Rgds KZ

--

Dave Peterson