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

April2020

New Member
Joined
Apr 4, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

ABCDEFGHIJK
6​
TableHeader1TableHeader2TableHeader3TableHeader4TableHeader5TableHeader6TableHeader7TableHeader8TableHeader9TableHeader10TableHeader11
7​
8​
starting active cell
9 new row​
=c8=c8ending active cell=h8=i8=j8


thank you - any help at all is much appreciated
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Try:
VBA Code:
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

New Member
Joined
Apr 4, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Try:
VBA Code:
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
BCDEFGHIJK
6TableHeader1TableHeader2TableHeader3TableHeader4TableHeader5TableHeader6TableHeader7TableHeader8TableHeader9TableHeader10TableHeader11
7=$h$8=$i$8text
8texttextstarting active cell=$h$8=$i$8
9 new row=$c$8=$c$8ending 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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,633
Messages
5,626,004
Members
416,151
Latest member
Openminded intellectual

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top