Automate Daily Reports to master sheet

[DrChu]

Hey guys,

I get extensive daily reports in the form of workbooks with multiple sheets. I have a master workbook with multiple sheets as well. We operate by shift numbers.

In the master workbook, I have shift numbers in columns. For example, row 8 is shift 5, row 9 is shift 6, row 10 is shift 7 and so on.

For example, I have data in C4:K4 that all have different cell references in the shift report #4 . I want the same cells to be referenced, one row down, but from a different workbook, shift #5 .
Right now, I copy down the formulas and change the source name manually, which is quite time consuming as I have to do this for 50+ cells across multiple worksheets. All of the reports I get are named after their shift numbers.

Here is what I do, in code form.

Sub update()'
' update Macro
'


'
    Range("B8:F8").Select
    Selection.AutoFill Destination:=Range("B8:F9"), Type:=xlFillDefault
    Range("B8:F9").Select
    Range("B9").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\owner\Desktop\Shift Reports\2019 SR\[5.xlsx]SUMMARY REPORT'!R15C2"
    Range("C9").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\owner\Desktop\Shift Reports\2019 SR\[5.xlsx]SUMMARY REPORT'!R17C2"
    Range("D9").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\owner\Desktop\Shift Reports\2019 SR\[5.xlsx]SUMMARY REPORT'!R16C2"
    Range("E9").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\owner\Desktop\Shift Reports\2019 SR\[5.xlsx]SUMMARY REPORT'!R18C2"
    Range("F9").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\owner\Desktop\Shift Reports\2019 SR\[5.xlsx]SUMMARY REPORT'!R10C8"
    Range("I8").Select
    Selection.AutoFill Destination:=Range("I8:I9"), Type:=xlFillDefault
    Range("I8:I9").Select
    Range("I9").Select
    ActiveCell.FormulaR1C1 = _
        "='C:\Users\owner\Desktop\Shift Reports\2019 SR\[5.xlsx]SUMMARY REPORT'!R16C8"
    Range("I10").Select
End Sub

This is only the code for one of the sheets, but an answer here would help me immensely for the bigger macro I'm hoping to create.

There has to be a way to make the [S#] a variable? In Column B of that sheet, I have the shift #. For example, Cell B8 has the value of "5", the shift number of the data appearing in that row. I'm just lost as to how to do it.

Thanks for your time.

 

[DrChu]

Bump