Code upgrade needed to fill a sheet with data from another sheet using certain conditions

[kelly mort]

This has been the code that I have been using for some time now – provided by @DanteAmor.

Sub FillSheet()
    Dim LR&, i&
    Dim a, b, j&, nSum#, x, y, L&
    Dim datash As Worksheet, outsh As Worksheet
    Set outsh = Sheets("Outputdata")
    Set datash = Sheets("Data")

    LR = datash.Cells(Rows.Count, "B").End(xlUp).Row
    If LR < 7 Then LR = 7
    a = datash.Range("B7:AM" & LR).Value2
    Application.ScreenUpdating = False
    ReDim b(1 To UBound(a, 1), 1 To 11)
    
    outsh.Range("A7:C" & LR) = datash.Range("B7:D" & LR).Value
    x = outsh.Range("C7", outsh.Range("C" & Rows.Count).End(3)).Value2
    ReDim y(1 To UBound(a), 1 To 1)
    For L = 1 To UBound(a)
        Select Case x(L, 1)
            Case 3: y(L, 1) = "GROUP 1"
            Case 4: y(L, 1) = "GROUP 2"
            Case 5: y(L, 1) = "GROUP 3"
            Case 6: y(L, 1) = "GROUP 4"
            Case 7: y(L, 1) = "GROUP 5"
            Case 8: y(L, 1) = "GROUP 6"
            Case 9: y(L, 1) = "GROUP 7"
            Case 10: y(L, 1) = "GROUP 8"
            Case 11: y(L, 1) = "GROUP 9"
            Case 12: y(L, 1) = "GROUP 10"
            Case 13: y(L, 1) = "GROUP 11"
        End Select
    Next L
    outsh.Range("C7").Resize(UBound(y)).Value2 = y
    For i = 1 To UBound(a)
        nSum = 0
        For j = 8 To 17
            b(i, j - 7) = Round(a(i, j) * 0.5, 1) + Round(a(i, j + 10) * 0.5, 1)
            nSum = nSum + b(i, j - 7)
        Next j
        b(i, 11) = nSum
    Next i
    outsh.Range("D7").Resize(UBound(b, 1), UBound(b, 2)).Value = b
    outsh.Range("D7:O" & LR).Replace 0, "", xlWhole, , , , False, False
    Application.ScreenUpdating = True
End Sub

Now my data set has grown to column AR and the new requirements are as follows:



Under AQ are strings in the form "Year1/Year2" eg 2020/2021
Then AR has strings like "LEVEL 1".

So under 2020/2021, there will or can be LEVEL 1, LEVEL 2, LEVEL 3 and so on.

Then the same thing could happen to 2021/2022 and so on.

So a criterion in column D (on the datash), say 3, having an ID of 7, could exit 3 times the number of different year ranges in column AQ as described above.

That's 2020/2021 will have

B     C                 D         ......    AQ                AR
ID    Name         Cat      ......    Year              LEVEL

7      Ben 10        3         ......    2020/2021    LEVEL 1
7      Ben 10        3         ......    2020/2021    LEVEL 2
7      Ben 10        3         ......   2020/2021     LEVEL 3

Then when we get to 2021/2022, the same ID will appear .

Which year range and LEVEL to handle is supplied by these two variable :

MyYear = "2020/2021"
MyLevel = "LEVEL 1"

As examples.



In this case, I want to fill the outsh with data from the datash which are for LEVEL 1 of 2020/2021.

How do I tweak or amend the above code to get the job done for me?




I am having the feeling; this part of the code is the one that needs the tweak

a = datash.Range("B7:AM" & LR).Value2



outsh.Range("A7:C" & LR) = datash.Range("B7:D" & LR).Value

x = outsh.Range("C7", outsh.Range("C" & Rows.Count).End(3)).Value2

But I have no idea how to fix it.


Thanks in advance.
Kelly