|Let's start with the fact that your rarely need to actually Select a range or object for VBA to perform an action on it.|
You'll have to test this, but it looks to me as if your first 7 lines can be replaced with this single line (assuming Sheet1 is the right sheet in each book...modify as needed)
Workbooks("TEMP Release BoW.xlsx").Sheets(1).Range("AG3:AG207").Copy _
Destination:=Workbooks("TEMP Release Control Matrix.xlsx").Sheets(1).Range("S4")
As for your question, I'd just like to check something...
You say you want to fill Column M from M4 to the last cell in Column M that contains a value. Should I take that to mean that you want to overwrite what is currently in Column M with the "=Sx - 42" formula?
This code should do that for you. On the other hand, if you are looking to fill Column M up until the last cell in some other column just change the Column letter in the lastRw line of this code:
(By using the R1C1 notation, RC tells VBA to retain the Row number of where formula is placed but increment the Column number by 6 (M + 6 = S)
'Determine last cell with data in Column B
lastRw = Range("M" & Rows.Count).End(xlUp).Row
'Fill M4:M(last row) with formula
Range("M4:M" & lastRw).FormulaR1C1 = "=RC-42"
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.