It is just a matter of iterating the sheets. Of course protection may need to be addressed if used. I normally do that in the workbook open event so that code can make changes.
Code:
Sub Main()
Dim ws As Worksheet, c As Long, s$
For Each ws In Worksheets
With ws
c = .Cells(.Rows.Count, "B").End(xlUp).Row
s = "=Right(B4,5) & " & """" & " " & """" & " & C4"
'Debug.Print s
.Range("A4").Formula = s
.Range("A4").Copy .Range(.Range("A4"), .Cells(c, "A"))
End With
Next ws
End Sub
I normally use a string variable to build a formula to easily debug if needed.