فصل البنون عن البنات من جدول مجمع إلى جداول منفردة

Sub Test_YasserKhalil()

    Dim a, aBoys, aBoysA, aGirls, aGirlsA, ws As Worksheet, sh As Worksheet, sGrade As String, i As Long, m As Long, n As Long

    Application.ScreenUpdating = False

        Set ws = ThisWorkbook.Worksheets("Data")

        Set sh = ThisWorkbook.Worksheets("Search")

        a = ws.Range("A2:K" & ws.Cells(Rows.Count, "K").End(xlUp).Row).Value

        sGrade = sh.Range("C2").Value

        With sh

            .Range("M10:N" & Rows.Count).ClearContents

            .Range("P10:T" & Rows.Count).ClearContents

            .Range("V10:X" & Rows.Count).ClearContents

        End With

        ReDim aBoys(1 To UBound(a, 1), 1 To 2)

        ReDim aBoysA(1 To UBound(a, 1), 1 To 3)

        ReDim aGirls(1 To UBound(a, 1), 1 To 2)

        ReDim aGirlsA(1 To UBound(a, 1), 1 To 3)

        For i = LBound(a) To UBound(a)

            If a(i, 9) = "ذكر" And a(i, 11) = sGrade Then

                m = m + 1

                aBoys(m, 1) = m: aBoys(m, 2) = a(i, 3)

                aBoysA(m, 1) = a(i, 4): aBoysA(m, 2) = a(i, 7):: aBoysA(m, 3) = a(i, 5)

            ElseIf a(i, 9) = "انثى" And a(i, 11) = sGrade Then

                n = n + 1

                aGirls(n, 1) = n: aGirls(n, 2) = a(i, 3)

                aGirlsA(n, 1) = a(i, 4): aGirlsA(n, 2) = a(i, 7):: aGirlsA(n, 3) = a(i, 5)

            End If

        Next i

        sh.Range("M10").Resize(UBound(aBoys, 1), UBound(aBoys, 2)).Value = aBoys

        sh.Range("P10").Resize(UBound(aBoysA, 1), UBound(aBoysA, 2)).Value = aBoysA

        sh.Range("S10").Resize(UBound(aGirls, 1), UBound(aGirls, 2)).Value = aGirls

        sh.Range("V10").Resize(UBound(aGirlsA, 1), UBound(aGirlsA, 2)).Value = aGirlsA

    Application.ScreenUpdating = True

End Sub