Macro - Insert Links in Multiple Cells to Multiple Workbooks

[ElizS]

I’m trying to create a macro to look through multiple workbooks that are daily logbooks and have the number in a particular cell from each work book be shown next to the date of the external workbook.

Each workbook follows the same naming convention of Year_Month_Day Shift Log and the data I need is in cell R76 of worksheet LogBook. E.g. the workbook from 6th May would be 2017_05_06 Shift Log.xlsm.

I’ve managed to find some code that gets me part way there but at the moment it only looks at specific cells to modify the external link and gives me the link I need. It doesn’t loop and then look at the next row/date and currently it gives me the link but ideally I would just want the number that the linked cell refers to.

There are hundreds of workbooks to be linked and future workbooks that haven’t even been created yet but I will need to find the data in future too.

Here is the code I’m currently working with:

Sub LogBookSearch()
'
Dim ThisDay As String
Dim ThisMonth As String
Dim ThisYear As String

ThisDay = Range("C2").Value
ThisMonth = Range("B2").Value
ThisYear = Range("A2").Value


With Range("D2")
.Formula = "='U:\[" & ThisYear & "_" & ThisMonth & "_" & ThisDay & " Shift Log.xlsm]LogBook'!$R$76"

End With

End Sub


Here is the workbook (simplified) I would like the data posted to (in the current macro D2 shows the linked figure but the macro posts it as a link rather than just the figure):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]Day[/TD]
[TD]Linked Data[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2017[/TD]
[TD]05[/TD]
[TD]05[/TD]
[TD]='U:\[2017_05_05 Shift Log.xlsm]LogBook'!$R$76[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2017[/TD]
[TD]05[/TD]
[TD]06[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2017[/TD]
[TD]05[/TD]
[TD]07[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2017[/TD]
[TD]05[/TD]
[TD]08[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2017[/TD]
[TD]05[/TD]
[TD]09[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2017[/TD]
[TD]05[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody></tbody>[/TABLE]

Any help to put me in the right direction of how to loop my marco to the next row and post the linked figure rather than the link itself would be greatly appreciated.