Macro for copying value to specific cells

[Ivica1987]

Hello

Since I don't use much Excel Macro I will need help with a VBA Macro for Excel. What I need is to copy a specific value from the 5th row (the Main Value) depending which month it is (cell K1). As an example: if the month is equal to number 9 then it will copy the value from the cell for September (cell AD5: 125068) to the column D for each of the three employees (the orange fields).

I can maybe solve this with normal formulas and with the VBA function for recording, but it could happen that next month the number of employees is different and so the recorded macro wouldn't work. Also, I will need something that works on multiple sheets since every table in every sheet has the same layout.

Here is an example.

Thanks in advance and best regards

 

[shreif]

Here is an example with multiple sheets. Link.

Sub do_it()
    Dim x As Long
    x = Range("a" & Rows.Count).End(xlUp).Row
    Columns(4).NumberFormat = "general"
    Select Case x
        Case Is > 7
            Range("a7", Range("a7").End(xlDown)).Select
            Selection.Offset(, 3).FormulaArray = "=INDEX(R5C6:R5C41,MATCH(R1C11,MONTH(R4C6:R4C41),0))"
        Case Is = 7
            Range("d7").FormulaArray = "=INDEX(R5C6:R5C41,MATCH(R1C11,MONTH(R4C6:R4C41),0))"
        Case Is < 7
            Exit Sub
    End Select
End Sub

Sub Sheets()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        Call do_it
    Next ws
End Sub

 

[Ivica1987]

Thanks for the code. I have for now good results. I will tomorrow post a feedback.

Best regards