Hi,
I am quite new to VBA and I would like to implement an automation.
I have a workbook with several sheets :
-> Overview sheet
-> 20 sheets named after the staff number of each worker
-> A few additional sheets that are not relevant
In the overview sheet, I have the staff number of each worker in column C (C5 to C25 but it can variable month after month).
Example
In column D, labelled "Cost 1", I would like the macro to recognise the staff number sheet thanks to the staff number mentioned in cell C5 and paste the sum of values of cells L52 and M52 from the staff number sheet in D5.
In column E, labelled "Cost 2", I would like the macro to recognise the staff number sheet thanks to staff number mentioned in cell C5 and paste value of cell N52 from the staff number sheet in E5.
I have many more columns in the Overview sheet to populate but if I have a basis for the two examples below, I could try to practice by myself for those.
Here's the current status of my code but I am stuck at this point :
Sub CompleteCells()
Dim ws As Worksheet, sh As Worksheet
Dim Rng As Range, c As Range
Set ws = Sheets("Overview")
Set Rng = ws.Range("C5:C100")
For Each sh In Sheets
For Each c in Rng.Cells
If sh.Name = c Then
.Copy Destination:=ws.Cells(52, LColumn)
End if
Next c
Next sh
End Sub
Thank you very much for your valuable help !
I am quite new to VBA and I would like to implement an automation.
I have a workbook with several sheets :
-> Overview sheet
-> 20 sheets named after the staff number of each worker
-> A few additional sheets that are not relevant
In the overview sheet, I have the staff number of each worker in column C (C5 to C25 but it can variable month after month).
Example
In column D, labelled "Cost 1", I would like the macro to recognise the staff number sheet thanks to the staff number mentioned in cell C5 and paste the sum of values of cells L52 and M52 from the staff number sheet in D5.
In column E, labelled "Cost 2", I would like the macro to recognise the staff number sheet thanks to staff number mentioned in cell C5 and paste value of cell N52 from the staff number sheet in E5.
I have many more columns in the Overview sheet to populate but if I have a basis for the two examples below, I could try to practice by myself for those.
Here's the current status of my code but I am stuck at this point :
Sub CompleteCells()
Dim ws As Worksheet, sh As Worksheet
Dim Rng As Range, c As Range
Set ws = Sheets("Overview")
Set Rng = ws.Range("C5:C100")
For Each sh In Sheets
For Each c in Rng.Cells
If sh.Name = c Then
.Copy Destination:=ws.Cells(52, LColumn)
End if
Next c
Next sh
End Sub
Thank you very much for your valuable help !