Sort the worksheet content accordingly

[ddddd123]

How to automatically sort the order of the data in worksheet 2 according to the numbers entered in C4 in worksheet 1.
i.e.
if enter 1 in C4 then sort by name
if enter 2 in C4 then sort by student ID
if enter 3 in C4 then sort by score

I made some attempts, but there have some problem, it can't sort on the fly after I type the numbers, it needs to manually execute the macro every time to work.

Sub Sort()
    If Worksheets("Worksheets 1").Cells(4, 3).Value = 1 Then
        Range("Worksheets 2").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
    
    ElseIf Worksheets("Worksheets 1").Cells(4, 3).Value = 2 Then
        Range("Worksheets 2").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo
    
    ElseIf Worksheets("Worksheets 1").Cells(4, 3).Value = 3 Then
        Range("Worksheets 2").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlNo
    End If
End Sub

This is what worksheet 1 looks like

This is what worksheet 2 looks like
Column A is name, Column B is student ID, Cloumn C is score.

 

[Joe4]

If you want it to happen automatically when you manually update cell C4 on sheets "Worksheet 1", then you need to use an Event Procedure code.
Go to "Worksheet 1", right-click on the sheet tab name at the bottom of the screen, select View Code, and paste this code into the VB Editor window that pops up (the VBA ABSOLUTELY MOST go in this sheet module to work automatically):

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if update not to cell C4
    If Intersect(Target, Range("C4")) Is Nothing Then Exit Sub

'   Determine how to sort
    Select Case Range("C4").Value
        Case 1
            Sheets("Worksheets 2").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
        Case 2
            Sheets("Worksheets 2").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo
        Case 3
            Sheets("Worksheets 2").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlNo
    End Select

End Sub

 

[ddddd123]

如果您希望它在您手動更新工作表“工作表 1”上的單元格 C4 時自動發生，那麼您需要使用事件過程代碼。
進入“工作表1”，在屏幕底部的工作表選項卡名稱上單擊鼠標右鍵，選擇查看代碼，然後將此代碼粘貼到彈出的VB編輯器窗口中（VBA絕對最多進入此工作表模塊工作自動地）：

Private Sub Worksheet_Change(ByVal Target As Range)

' 如果不更新到單元格 C4，則退出
    如果 Intersect(Target, Range("C4")) 什麼都不是，則退出 Sub

' 確定如何排序
    選擇案例範圍（“C4”）。值
        情況1
            Sheets("Worksheets 2").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
        案例2
            Sheets("Worksheets 2").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo
        案例3
            Sheets("Worksheets 2").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlNo
    結束選擇

結束子

Thank you! But after I paste this code into the VB Editor window, I still need to manually execute the macro to update the "Worksheet 2"

 

[Joe4]

What is the EXACT name of each worksheet?
Is it really "Worksheets 1" and "Worksheets 2"?

Was your original code working?
I don't see how it can, as I don't see a valid worksheet/named range reference, so I am not sure it knows what range to sort.

What is the name of the VBA module where you have placed this code?

By the way, you should NEVER use reserved words (words used by Excel/VBA for functions, properties, objects, etc) like "Sort" as the name of your procedures, functions, or variables.
Doing so can cause errors and unexpected results.

 

[ddddd123]

Thank you for your reminder!

Now when I enter numbers in C4, it says "No named argument found", for this I changed the name of Worksheets 2 to Data, but that still doesn't work. I'm not sure where I went wrong

 

[Joe4]

I think the issue is with your sort commands.
It isn't enough to provide the sheet name, you also have to provide the range you wish to sort.

 

[ddddd123]

Can I use
Dim Rng as Range
Set Rng = Sheets("Data").Range("A1:C6").Value
to finish that?

 

[Joe4]

You also need a sheet reference on your Sort key.

So your code should look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if update not to cell C4
    If Intersect(Target, Range("C4")) Is Nothing Then Exit Sub

'   Determine how to sort
    Select Case Range("C4").Value
'       Determine sort
        Case 1
            Sheets("Data").Columns("A:C").Sort Key1:=Sheets("Data").Range("A1"), Order1:=xlAscending, Header:=xlNo
        Case 2
            Sheets("Data").Columns("A:C").Sort Key1:=Sheets("Data").Range("B1"), Order1:=xlAscending, Header:=xlNo
        Case 3
            Sheets("Data").Columns("A:C").Sort Key1:=Sheets("Data").Range("C1"), Order1:=xlAscending, Header:=xlNo
    End Select

End Sub

If you want to limit the range to sort, you can change the

.Columns("A:C")

reference to

.Range("A1:C6")

 

[ddddd123]

Awesome. Thank you very much!!! Your solution is very detailed, this really helped me a lot

 

[Joe4]

You are welcome!

Glad I was able to help.