Help with Sorting

Hi, need to sort data from several sheet to individual activity sheet

Thank you

Hi AliGW,

Im using Excel 2019

Thank you

Done, Thank you very much for reminding me

One way would be to use VBA.

In the attached version of your file I've added the code shown below and a button on the first sheet to run it when clicked.

Worth noting, this is all based on guesswork as there wasn't a whole heap of information in your original post about what your desired outcome actually is.

Dim ws As Worksheet

Sub CompileDataSheets()
    With ThisWorkbook
        Set ws = Sheet1
        Call MoveData
        
        Set ws = Sheet2
        Call MoveData
        
        Set ws = Sheet3
        Call MoveData
        
        Set ws = Nothing
    End With
End Sub

Sub MoveData()
    Dim c As Long, r As Long
    Dim rng As Range
    
    With ws
        For c = 3 To 11
            For r = 6 To .Cells(Rows.Count, 1).End(xlUp).Row
                If .Cells(r, c) = 1 And .Cells(r, 2) <> "" Then
                    With Sheets(.Cells(1, c).Value)
                        Set rng = .Cells(Rows.Count, 1).End(xlUp).Offset(1)
                        rng = ws.Cells(r, 1)
                        rng.Offset(, 1) = ws.Cells(r, 2)
                        rng.Offset(, 2) = ws.Cells(2, 1)
                    End With
                End If
            Next r
        Next c
    End With
    
    MsgBox "All done.", , ""
End Sub

For some reason it's not adding one of the names as I'd expect it to, but I need to go and cook dinner so can't look into the reason for that right now.

If this is any use to you I will come back and find why the one name isn't quite working then will update the code.

BSB

Found the issue mentioned above. It's a formatting thing. Need to change the font colour and style in B6 on the Badminton sheet to match the cell above.

BSB

Originally Posted by Abdul Rahman

do I have to add some code Set ws = Sheet4 Call MoveData

Yes you can simply add more lines to that section. Just make sure the sheet number matches the codename of each one.
If your workbook was set up with all the class sheets at the start and no hidden sheets in between, the code could be adapted to loop through those rather than need to specify them in code. I was going to do that in the example file, but noticed some hidden sheets.
You could also loop through the sheets and only process any where the tab name starts in "Year" for example. That way you wouldn't have to add anything to the code and it wouldn't matter which order the sheets were in.
As I said, I was working from pure guesses as not much info provided.

Originally Posted by Abdul Rahman

and for the result, can the number run normally from 1 onwards

I'm not sure what you mean by this. Could you elaborate?

BSB

So you want the numbers to just start at 1 and increment, rather than use the numbers from the class sheets?

BSB

Try this slightly amended version.

Dim ws As Worksheet

Sub CompileDataSheets()
    With ThisWorkbook
        Set ws = Sheet1
        Call MoveData
        
        Set ws = Sheet2
        Call MoveData
        
        Set ws = Sheet3
        Call MoveData
        
        Set ws = Nothing
    End With

    MsgBox "All done.", , ""
End Sub

Sub MoveData()
    Dim c As Long, r As Long
    Dim rng As Range
    
    With ws
        For c = 3 To 11
            For r = 6 To .Cells(Rows.Count, 1).End(xlUp).Row
                If .Cells(r, c) = 1 And .Cells(r, 2) <> "" Then
                    With Sheets(.Cells(1, c).Value)
                        Set rng = .Cells(Rows.Count, 1).End(xlUp).Offset(1)
                        Select Case rng.Offset(-1)
                            Case "No": rng = 1
                            Case Else: rng = rng.Offset(-1) + 1
                        End Select
                        rng.Offset(, 1) = ws.Cells(r, 2)
                        rng.Offset(, 2) = ws.Cells(2, 1)
                    End With
                End If
            Next r
        Next c
    End With
End Sub

Note I also moved the line for the pop up message box so it only appears once. I'd put it in completely the wrong place before because I added it after I'd tested it.

BSB

Yes Sir/Madam, that was amazing, help me a lot.

Thank you very much.

I will continue my work tomorrow, need to rest now, its nearly 4 am my time.

I will come back to you if there is any updates.

Anyway good night and sleep tide Sir/Madam.

Glad I could help.

Enjoy your sleep.

I shall check back tomorrow to see if there's further help required.

BSB

Hi Sir/Madam.

I was just notice that the result of the data n the Year row only come out with Year*A no B or C

how would I add some code to VBA?

Thank you

Hi Sir/Madam Gd Evening

Hows your day

I just noticed that there are so many hidden sheets. I just helping my wife finishing their school task so I had deleted all unnecessary sheets

Task is almost finish, just wanted to add some command to clear data if maybe they want to add some more data.

Thank you very much for your help

I post yesterday for some answer but no reply so I try to finish it.

I made it so I marked as SOLVED

but today I realised that there are some miss check on my side

Im sorry for the inconvenience caused

So how do I do now? to repost back

Thank you

just click Mark this thread as unsolved

Done. Thank you Madam