Same code in multiple cells on multiple sheets

[CookieMonster76]

Hi

I have created the following to put the code in cells on multiple sheets:

For Each ws In Sheets(Array("C-H-1218 Jenner", "C-H-1219 St Peters", "C-H-1235 Moxley"))

With ws.Range("C12")
.FormulaR1C1 = "=-ROUND(SUMIFS('TB actual'!C4,'TB actual'!C3,RC[4],'TB actual'!C[-2],R3C2,0)"
.Value2 = .Value2
End With

With ws.Range("C13")
.FormulaR1C1 = "=-ROUND(SUMIFS('TB actual'!C4,'TB actual'!C3,RC[4],'TB actual'!C[-2],R3C2,0)"
.Value2 = .Value2
End With

With ws.Range("C16")
.FormulaR1C1 = "=-ROUND(SUMIFS('TB actual'!C4,'TB actual'!C3,RC[4],'TB actual'!C[-2],R3C2),0)"
.Value2 = .Value2
End With

next ws

Sine the code in the cells is the same, is there a way to reduce the lines of code on this (I am thinking specify the cells as an array also, but I don't know how to combine this with the array for the sheets)?

Thanks

Paul

 

[Special-K99]

Try

For Each ws In Sheets(Array("C-H-1218 Jenner", "C-H-1219 St Peters", "C-H-1235 Moxley"))

With ws.Range("C12, C13, C16")
.FormulaR1C1 = "=-ROUND(SUMIFS('TB actual'!C4,'TB actual'!C3,RC[4],'TB actual'!C[-2],R3C2,0)"
.Value2 = .Value2
End With

next ws

 

[ranman256]

Sub MyMAcro()
For Each ws In Sheets(Array("C-H-1218 Jenner", "C-H-1219 St Peters", "C-H-1235 Moxley"))
    SetRng ws.Range("C12")
    SetRng ws.Range("C13")
    SetRng ws.Range("C16")
Next ws
End Sub




Public Sub SetRng(pRng As Range)
With pRng
.FormulaR1C1 = "=-ROUND(SUMIFS('TB actual'!C4,'TB actual'!C3,RC[4],'TB actual'!C[-2],R3C2,0)"
.Value2 = .Value2
End With
End Sub