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

2Kittens

New Member
Joined
Apr 22, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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 !
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
ABCDE
1
2
3
4Staff NumberCost 1Cost 2
5staff 12313
6staff 24323
7staff 36333
8staff …
overview
Cell Formulas
RangeFormula
D5:D7D5=INDIRECT("'" & C5 & "'!L52")+INDIRECT("'" & C5 & "'!M52")
E5:E7E5=INDIRECT("'" & C5 & "'!n52")


Example sheets:
Dante Amor
ALMN
1
51
52111213
staff 1

Dante Amor
ALMN
1
51
52212223
staff 2

Dante Amor
ALMN
1
51
52313233
staff 3


But if you want it with macro.
The following does not use loops:
VBA Code:
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:

VBA Code:
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
--------------
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,121
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top