EXCEL DATA TABLE HELP In excel I need to create two related data tables. One dat

Question

EXCEL DATA TABLE HELP

In excel I need to create two related data tables. One data table shows the active players of a sports team and the other shows the inactive members of a sports team. Assuming the three columns are "status", "first name" and "last name":

I need to have the tables automatically sort alphabetically by last name if any changes are made, how can I do this? I know how to sort manually, but not automatically. If possible, how could you sort by first name in the event players had the same last name?

Also, all the players would start on the "Active" data table and the status of the players would all start as "active", how could I get the player's data to transfer to the other table automatically if their status was changed to "inactive"?

Thank you!

Explanation / Answer

Private Sub Excel_Sort(ByVal Target As Range)
On Error Resume Next // error handling
Range("C1").Sort Key1:=Range("C2"), _ //comparing B1 cell value which we assume to suppose contain lastname
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom //moving to up when sorting condition success
End Sub

---------------------------------------------------------------------------------------------------------------------------

Sub Active_players() //method starts from here
Dim Check As Range, count As Long, row2 As Long, row1 As Long //declaring required variables from here
Application.ScreenUpdating = False
row1 = Worksheets("worksheet1").UsedRange.Rows.Count //sheet1 counting rows
row2 = Worksheets("worksheet2").UsedRange.Rows.Count //sheet2 counting rows
If row2 = 1 Then row2 = 0
For count = row1 To 2 Step -1
If Range("C" & count).Value = "InActive" Then

//Here I used C because only three columns are there.status,first name and lastname
Rows(count).Cut Destination:=Worksheets("Sheet2").Range("A" & row2 + 1)

//Cut the row and paste to worksheet
row2 = row2 + 1
Else:
End If //If loop ends
Next count
Application.ScreenUpdating = True
End Sub

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.