VBA pull first 4 digits until the bottom of the data

[mdmackillop]

Sub test()

Dim r1 As Range, r2 As Range
Set r1 = Sheet1.Columns(1).SpecialCells(2)
Set r2 = Sheet2.Range(r1.Address)
r2.FormulaR1C1 = "=LEFT(Sheet1!RC,4)*1"
With Sheet2.UsedRange.Columns(1)
    .Value = .Value
End With
End Sub