Copying data to another sheet and sorting it automatically

[RJQMAN]

I have a question about programming a sort function for data from a
sporting event. I am not sure where to ask the question, but I would
really appreciate some help. I posted it on the base newsgroup too -
please forgive the multiple posts.

I would like to read data on one page, automatically sort it, and print
it on another worksheet in the same workbook without changing the data
on the original page. This is information for an announcer at a
sporting event, and I do not want the announcer to have to do manual
excel operations, but I could use a button for a VB sort if I had to.
I would prefer not to.

Here is the scenario. I have several columns of data on the second
worksheet in a workbook. The data is in columns C, Column D, Column Y,
and column Z. The data in each of these columns has been calculated
from data entered manually at the sporting event into the first
worksheet in the workbook.

I need to take these four columns of data from the second page to the
third page of the workbook, and display it in sorted order, with the
sorting based on the numeric data in column Z. Then I need to take it
to the fourth page, and display it sorted based on the numeric data in
column AA.

Here is an example of the way the data is displayed on the 2nd
Worksheet;

Column C Column D Column Y Column Z ColumnAA

GROUP NAME SCORE RANK IN GROUP OVERALL RANK

Group A Jones 22.5 6 8
Group A Smith 22.9 5 7
Group A Kelly 50 2 2
Group A Brown 44 4 4
Group A White 50 2 2
Group A Scarlet 65 1 1
Group B Tommy 33 2 6
Group B Jane 44 1 4

Here is how I would like to have it displayed on the third worksheet -
sorted by group and then by rank in the group. When there is a tie, I
do not care which name comes first.

GROUP NAME SCORE RANK IN GROUP

Group A Scarlett 65 1
Group A White 50 2
Group A Kelly 50 2
Group A Brown 44 4
Group A Smith 22.9 5
Group A Jones 22.5 6

Group B Jane 44 1
Group B Tommy 33 2


I would also like to have it displayed on the 4th worksheet like this;

NAME SCORE OVERALL RANK

Scarlett 65 1
White 50 2
Kelly 50 2
Brown 44 4
Jane 44 4
Tommy 33 6
Smith 22.9 7
Jones 22.5 8

Once again, when there is a tie, I do not care which name is first. I
know there is a way to do this, but I need help. I am a beginner at
best, but I am learning. Can anyone help me with the code?

Thanks in advance.

 

[Guest]

I'm not sure how experienced they are. Therefore, I've emailed them an Excel
file with a "command bar control" so all they would need to do is click it to
run the following code. If this goes well for them me and this group should
continue on to solve the remainder of her problem.

Range("C,Y:Y,Z:Z,AA:AA").Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("D1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

 

[Guest]

I've now sent them a file containing macros to do both sorts. They do have a
couple draw backs that may need to be addressed.

1) User will need to be on original sheet when selecting the controls.

2) Poor control face id's.

Sub Macro1()
Range("C,Y:Y,Z:Z,AA:AA").Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("D1") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
End Sub

Sub Macro2()
Range("D,Y:Y,AA:AA").Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Key2:=Range("B1") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
End Sub