Copying formula to the next row when inserting a new row

Question

I have a row in which there are formulas using values of the same row  The next row is empty  just with a different background color   Now  if I insert a new row  by right-clicking on the empty row and  insert    I get a new row with NO background color  which is what I want   but the line also does NOT contain any formulas  how do I get Excel to be smarter and copying the formulas from the previous row  when creating the new row   One more piece of information  data validation information  i e  a drop down list  is copied when inserting the new row   Thanks

User · Answer

You need to insert the new row and then copy from the source row to the newly inserted row. Excel allows you to paste special just formulas. So in Excel:

Insert the new row
Copy the source row
Select the newly created target row, right click and paste special
Paste as formulas

VBA if required with Rows("1:1") being source and Rows("2:2") being target:

Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("2:2").Clear

Rows("1:1").Copy
Rows("2:2").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone

User · Answer

Private Sub Worksheet Change ByVal Target As Range    data starts on row 3 which has the formulas  the sheet is protected - input cells not locked - formula cells locked  this routine is triggered on change of any cell on the worksheet so first check if   it s a cell that we re interested in - and the row doesn t already have formulas If Target Column   3 And Target Row  gt  3   And Range  M   amp  Target Row  Formula      Then      On Error GoTo ERROR OCCURRED       unprotect the sheet - otherwise can t copy and paste     ActiveSheet Unprotect      disable events - this prevents this routine from triggering again when      copy and paste below changes the cell values     Application EnableEvents   False       copy col D  with validation list  from row above to new row  not locked      Range  D   amp  Target Row - 1  Copy     Range  D   amp  Target Row  PasteSpecial       copy col M to P  with formulas  from row above to new row     Range  M   amp  Target Row - 1  amp    P   amp  Target Row - 1  Copy     Range  M   amp  Target Row  PasteSpecial    make sure if an error occurs  or not  events are re-enabled and sheet re-protected  ERROR OCCURRED       If Err Number  lt  gt  0 Then         MsgBox  An error occurred  Formulas may not have been copied    amp  vbCrLf  amp  vbCrLf  amp                Err Number  amp    -    amp  Err Description     End If       re-enable events     Application EnableEvents   True      re-protect the sheet     ActiveSheet Protect       put focus back on the next cell after routine was triggered     Range  D   amp  Target Row  Select  End If   End Sub

User · Answer

If you have a worksheet with many rows that all contain the formula  by far the easiest method is to copy a row that is without data  but it does contain formulas   and then  insert copied cells  below above the row where you want to add   The formulas remain  In a pinch  it is OK to use a row with data   Just clear it or overwrite it after pasting

User · Answer

One other key thing that I found regarding copying rows within a table  is that the worksheet you are working on needs to be activated  If you have a workbook with multiple sheets  you need to save the sheet you called the macro from  and then activate the sheet with the table  Once you are done  you can re-activate the original sheet   You can use Application ScreenUpdating   False to make sure the user doesn t see that you are switching worksheets within your macro   If you don t have the worksheet activated  the copy doesn t seem to work properly  i e  some stuff seem to work  and other stuff doesn t

User · Answer

Make the area with your data and formulas a Table     Then adding new information in the next line will copy all formulas in that table for the new line  Data validation will also be applied for the new row as it was for the whole column  This is indeed Excel being smarter with your data   NO VBA required

[excel] Copying formula to the next row when inserting a new row

Examples related to excel