you'd want something like this
For Each targetsheet In ThisWorkbook.Sheets
With targetsheet
If Left(.CodeName, 7) = "doCALC_" Then
.Cells(r, c).FormulaR1C1 = "=VLOOKUP(RC[-2],'[Yesterday.xls]" & .Name & "'!C17:C18,2,0)"
.Cells(r, c).FormulaR1C1 = "=VLOOKUP(RC[-2],'[Yesterday.xls]" & .Name & "'!C17:C19,3,0)"
.Cells(r, c).FormulaR1C1 = "=VLOOKUP(RC[-2],'[Yesterday.xls]" & .Name & "'!C17:C20,4,0)"
.Cells(r, c).FormulaR1C1 = "=VLOOKUP(RC[-2],'[Yesterday.xls]" & .Name & "'!C17:C21,5,0)"
End If
End With
Next targetsheet
the above will insert the formulas on any worksheet whose codename begins with "doCALC_". you'll have to specify the cells where the formulas want to go (where it says ".Cells(r,c)" in the code you'll need to replace r & c with the required row and column) as from your code there are obviously different formulae going into different cells, but they're referenced by ActiveCell and I'm guessing you trimmed the code.
Bookmarks