Macro to insert table row below and copy some results from above

[April2020]

Hi Everyone,

I am looking for a macro that would be able to perform this function in a table, with an example below

1. User starts with any cell in column E as the active cell
2. User presses Control+Shift+B to run the macro
3. Macro inserts a table row below the active cell
4. Macro inserts formulas into the newly created row that make the values in cells C, D, H, I and J equal to the cells in the same columns one cell above
5. Macro sets the active cell to column E in the newly created row

	A	B	C	D	E	F	G	H	I	J	K
6​	TableHeader1	TableHeader2	TableHeader3	TableHeader4	TableHeader5	TableHeader6	TableHeader7	TableHeader8	TableHeader9	TableHeader10	TableHeader11
7​
8​					starting active cell
9 new row​			=c8	=c8	ending active cell			=h8	=i8	=j8

thank you - any help at all is much appreciated

 

[mumps]

Try:

Sub InsertRow()
    Application.ScreenUpdating = False
    Rows(ActiveCell.Row + 1).Insert
    Range("C" & ActiveCell.Row + 1).Formula = "=" & Range("C" & ActiveCell.Row).Address
    Range("D" & ActiveCell.Row + 1).Formula = "=" & Range("D" & ActiveCell.Row).Address
    Range("H" & ActiveCell.Row + 1).Formula = "=" & Range("H" & ActiveCell.Row).Address
    Range("I" & ActiveCell.Row + 1).Formula = "=" & Range("I" & ActiveCell.Row).Address
    Range("J" & ActiveCell.Row + 1).Formula = "=" & Range("J" & ActiveCell.Row).Address
    Application.ScreenUpdating = False
End Sub

 

[April2020]

Try:

Sub InsertRow()
    Application.ScreenUpdating = False
    Rows(ActiveCell.Row + 1).Insert
    Range("C" & ActiveCell.Row + 1).Formula = "=" & Range("C" & ActiveCell.Row).Address
    Range("D" & ActiveCell.Row + 1).Formula = "=" & Range("D" & ActiveCell.Row).Address
    Range("H" & ActiveCell.Row + 1).Formula = "=" & Range("H" & ActiveCell.Row).Address
    Range("I" & ActiveCell.Row + 1).Formula = "=" & Range("I" & ActiveCell.Row).Address
    Range("J" & ActiveCell.Row + 1).Formula = "=" & Range("J" & ActiveCell.Row).Address
    Application.ScreenUpdating = False
End Sub

Thank you - this comes very close. However, just one point; if all of the cells in the table column are blank to start (not including the header) then it inserts the formula into all of cells in the column, like below:

A	B	C	D	E	F	G	H	I	J	K
6	TableHeader1	TableHeader2	TableHeader3	TableHeader4	TableHeader5	TableHeader6	TableHeader7	TableHeader8	TableHeader9	TableHeader10	TableHeader11
7								=$h$8	=$i$8	text
8			text	text	starting active cell			=$h$8	=$i$8
9 new row			=$c$8	=$c$8	ending active cell			=$h$8	=$i$8	=$j$8

Would there be a way to avoid this and just paste the formula into the new row?

thank you again - very much appreciated