Excel macros - why does a random zero appear in the active cell at the time I run the macro?

Yes, that will work. For example:

Worksheets("Sheet1").Range("I11").Value = Worksheets("Defaults CS").Range("A1").Value

But I also note that your macro updates columns I and M, in rows 11,15,19, 23,27 and 32, which apart from the final 32 (which would be 31 if the sequence were followed) all increment by 4. So it might be possible to simplify the code even more.

Put the default values to go in column I in column A, rows 1-6  of sheet 'Defaults CS'.

Put the default values to go in column M in column B, rows 1-6  of sheet 'Defaults CS'.

Sub test()

    Dim i As Long, j As Long

    For i = 1 To 6

        j = 4 * i + 7

        If j = 31 Then j = 32

        Worksheets("Sheet1").Range("I" & j).Value = Worksheets("Defaults CS").Range("A" & i).Value

        Worksheets("Sheet1").Range("M" & j).Value = Worksheets("Defaults CS").Range("B" & i).Value

    Next i

End Sub

Thank you - this is really helpful!

If I was going to write this out (vs. recording it), would the following formula work?

Worksheets ("insert worksheet name").Range ("Insert Cell code"). Value = Worksheets ("Insert worksheet name".Range ("Insert cell code to be copied"). Value

So that others could update this (I won't be the only user) i think it would be easier to write out the code vs. record it but I'm not sure if the code above is the best way to get the cells to be updates with default values from another worksheet?

If you test this by making your starting cell in a few different columns you will find that it only puts a zero if you start in columns A:G, and if you start in column H it will be fine.

In the very first statement in your macro, you have not preselected a cell first, and  the statement says take the value from seven columns to the left (RC[-7]). For columns 7 or less (A:G), to prevent an error when it hits the left edge of the sheet,  it does a 'wrap-around' and gives you the value from the extreme right. So if you run it starting from F3 and then look at the resultant formula in that cell you will find it contains ='Defaults CS'!XFC3.

 So the solution is to make sure you select a cell first, before the line that adds the formula.

Also, and I understand that you recorded this, but it is generally bad form, or at least unnecessary, to 'select' a cell first. So the line pair:

    Range("I11").Select

    ActiveCell.FormulaR1C1 = "='Defaults CS'!R[-1]C[-5]"

can be reduced to

    Range("I11").FormulaR1C1 = "='Defaults CS'!R[-1]C[-5]"

and similarly for all the other lines.