View Single Post
  #1   Report Post  
Old October 15th 17, 08:31 PM posted to microsoft.public.excel.misc
mas3228 mas3228 is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2017
Posts: 1
Default 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.