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]

you didn't confirm the formula with CTRL+SHIFT ENTER

to do that select cell d7 then press F2 key and paste the formula
then press CTRL+SHIFT ENTER in simultaneous time

 

[Ivica1987]

I think I got it working now. The formula works as described. Now I need a macro to put the formula only on the fields which have a employee. In this case only on the three rows which have an employee.

Best regards

 

[shreif]

I think I got it working now. The formula works as described. Now I need a macro to put the formula only on the fields which have a employee. In this case only on the three rows which have an employee.

Best regards

Sub do_it()

    Range("D7").Select
    Selection.FormulaArray = "=INDEX(R5C6:R5C41,MATCH(R1C11,MONTH(R4C6:R4C41),0))"
    Selection.AutoFill Destination:=Range("D7:D9"), Type:=xlFillDefault
    Range("D7").Select
End Sub

 

[Ivica1987]

Thanks for the macro, but what if next month we have let's say 4 employees. How to put the formula only on rows which have a name and employee number (A and B columns) or are not empty since only the rows which have a name must have a value in column D, others don't.

Best reagrds

 

[shreif]

Thanks for the macro, but what if next month we have let's say 4 employees. How to put the formula only on rows which have a name and employee number (A and B columns) or are not empty since only the rows which have a name must have a value in column D, others don't.

Best reagrds

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

 

[Ivica1987]

Thank you for the code. I am still testing it, but for now it looks good.
I'll write a feedback here as soon I am done with the testing.

Best regards

 

[Ivica1987]

The code works as expected. Is it possible to make it work on multiple sheets?

Best regards

 

[shreif]

The code works as expected. Is it possible to make it work on multiple sheets?

Best regards

of course , first you need to loop through all worksheets in the workbook so, i want to ask you if all the sheets in the work book are same or just specified sheets
it going to be easier if you uploaded your workbook

 

[Ivica1987]

I need it to work for every sheet in the Excel file and that just with one Macro. I'll make a workbook with some "fictional" sheets since the workbook that needs these Macros has some sensitive data.
I'll post today the link for download.

Best regards

 

[Ivica1987]

Here is an example with multiple sheets. Link.