VBA - Look for a sheet thanks to a range of cells and copy paste values from the found worksheet

[2Kittens]

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 !

 

[DanteAmor]

Hello @2Kittens. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

IMPORTANT: If the sheet name is in cells C5 and below.

Then you can use the following formulas:

Dante Amor
	A	B	C	D	E
1
2
3
4			Staff Number	Cost 1	Cost 2
5			staff 1	23	13
6			staff 2	43	23
7			staff 3	63	33
8			staff …
overview

Cell Formulas
Range	Formula
D5:D7	D5	=INDIRECT("'" & C5 & "'!L52")+INDIRECT("'" & C5 & "'!M52")
E5:E7	E5	=INDIRECT("'" & C5 & "'!n52")

Example sheets:

Dante Amor
	A											L	M	N
1
51
52												11	12	13
staff 1

Dante Amor
	A											L	M	N
1
51
52												21	22	23
staff 2

Dante Amor
	A											L	M	N
1
51
52												31	32	33
staff 3

But if you want it with macro.
The following does not use loops:

Sub CompleteCells_v1()
  Dim lr As Long
  lr = Sheets("overview").Range("C" & Rows.Count).End(3).Row
  With Sheets("overview").Range("D5:D" & lr)
    .Formula = "=INDIRECT(""'"" & C5 & ""'!L52"")+INDIRECT(""'"" & C5 & ""'!M52"")"
    .Value = .Value
  End With
  With Sheets("overview").Range("E5:E" & lr)
    .Formula = "=INDIRECT(""'"" & C5 & ""'!N52"")"
    .Value = .Value
  End With
End Sub

With a loop:

Sub CompleteCells_v2()
  Dim c As Range
  For Each c In Sheets("overview").Range("C5", Sheets("overview").Range("C" & Rows.Count).End(3))
    c.Offset(, 1).Value = Sheets(c.Value).Range("L52").Value + Sheets(c.Value).Range("M52").Value
    c.Offset(, 2).Value = Sheets(c.Value).Range("N52").Value
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------