Updating a list based on a different list

[Excelquestion35]

Hi all,

Looking for some advice on how to approach the following problem. I have situation where I have to keep track of a masterfile which contains the list of first line managers (FLM) per customer. This list is updated by a macro when people come and go. Thus the pool of FLMs is changing over time.

Next to this, I have a request file in which temp employees will be requested. In this form they have to state who will be there FLM. (dropdown list) This should be an actual list of FLMs and should thus be taken from the master file.

Therefore, I am thinking about creating a macro that updates the FLMs based on the masterfile. I think a for loop is required here, but I am wondering how I can let this loop know:

• Who belongs to the what operation?
• The length (amount of rows) that belong to a certain operation?
• Another question but not belonging to the loop; the name of each operation is not completely identical to that in the master file inside the request file. How can I deal with this? Normally you would have the "contains" Text filter inside filtering.

I think it is best to first delete all FLMs in the request file and then look for the list of FLMs in the masterfile.

Below two pictures to give you an impression of how things look like currently:

Request file


Master file


I am completely aware the code is a bit messy, but I started from scratch and then got stuck:

Sub UpdatenFLM()
'
' UpdatenFLM Macro
'

'
    Windows("Copy of Site overview (003) - 2.xlsm").Activate
    inarr = ActiveSheet.Range("$B$1:$GZ:50")
    Operation = Sheets("Supervisor (leidinggevende)").Range("B1") 'Every first cell in a column is the operation, use the first as a variable
    


For i = 1 To 207
    Columns(i).Select
    ActiveCell.Offset(0, 0).Range("A1").Select
    Selection.Copy 'Copy the first operation name


    ActiveSheet.Unprotect 'deleting the current list of FLMs
    ActiveCell.Offset(1, 0).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
    Windows("Copy of Site overview (003) - 2.xlsm").Activate 'Look for the new list of FLMs in other sheet
    ActiveSheet.Range("$A$3:$AS$62").AutoFilter Field:=2, Criteria1:="=*Ricoh*" _
        , Operator:=xlAnd 'instead of looking for something that includes Ricoh, how can I change this to the variable without having it to be the exact match?
    
    Range("C52").Select 'First FLM found on this row
    Range(Selection, Selection.End(xlDown)).Select 'Get the whole list of FLMs belonging to this operation
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows( _
        "Kronos Centraal Formulier v3.2 - Template (zonder check) - RPA versie.xlsm"). _
        Activate 'Return to original sheet and then paste the updated list of FLMs below the name of the operation
    Range("B2").Select 'How can I make this dynamic based on the operation?
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
Next i

End Sub