Insert blank row in table using VBA and automatically populate active cell
 

Hi all,

I have the following VBA code that inserts a blank row at the top of the table and R4 then becomes the active cell.


Private Sub CommandButton1_Click()


Sheets("Data").Range("R4").Select
ActiveCell.EntireRow.Insert Shift:=xlDown

Range("R4").HorizontalAlignment = xlCenter


End Sub


When inserting a new row, I would like to populate R4 with the year following the previous year which is then in cell R5.

For example:

Before VBA code is executed:
R4: 2014
R5: 2013
R6: 2012


After VBA code is executed:
R4: 2015
R5: 2014
R6: 2013
R7: 2012

These cells are formatted as text.