VBA - create new Sheet + fill data in cell based on list

[WouzzieW]

Hi,

I have a VBA script running to add a new Sheet, fill it with a template (TEST Test2) and add the name of the specific employee based on a list.
The employee data is located on worksheet RRAV, column K provides the name of the employee.

What i want to do is populate cell B3 with the user code (RRAVcode) of the employee (column A in the RRAV worksheet).

To do so, i tried to add the 'Sheets(Sheets.Count).Range("B3") = RRAVcodes.Value' line, but this populates all user worksheets with the first user code in the list.

Any help is appreciated!

Sub CreateSheetsFromAList()
    Dim MyCell As Range, myRange As Range, RRAVcodes As Range
    Dim wsNew As Worksheet
     
    Set myRange = Sheets("RRAV").Range("K1")
    Set myRange = Range(myRange, myRange.End(xlDown))
    
    Set RRAVcodes = Sheets("RRAV").Range("A1")
    Set RRAVcodes = Range(RRAVcodes, RRAVcodes.End(xlDown))

    For Each MyCell In myRange
        Worksheets("Test TEST2").Copy after:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = MyCell.Value
        Sheets(Sheets.Count).Range("B3") = RRAVcodes.Value
                        
    Next MyCell
    
    End Sub