CODEWITHSUNDEEP
excel
Manu
Manu

Reputation: 4137

Copying formula to the next row when inserting a new row

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.

Upvotes: 21

Views: 183227

Answers (5)

Jenny S.
Jenny S.

Reputation: 77

This is a routine you can add to an Excel spreadsheet to do copy/paste of specific formulas whenever user modifies a line. You will need to adjust the letters inside this script for the specific columns that you want copied in your spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)

'Data starts on row 3, which has the formulas.
'The sheet is protected - input cells are not locked - formula cells are locked.
'This routine is triggered on change of any cell on the worksheet, so first check
'if it is a cell that we're interested in and that the row does not already have
'formulas.
If Target.Column = 3 And Target.Row > 3 _
And Range("M" & Target.Row).Formula = "" Then

    On Error GoTo ERROR_OCCURRED

    'Unprotect the sheet - otherwise we cannot copy and paste.
    ActiveSheet.Unprotect
    'Disable events in order to prevent this routine from triggering again when
    'Now copy and paste to change the cell values.
    Application.EnableEvents = False
    
    'Copy col D (with validation list) from row above to new row (not locked).
    Range("D" & Target.Row - 1).Copy
    Range("D" & Target.Row).PasteSpecial
    
    'Copy col M to P (with formulas) from row above to new row.
    Range("M" & Target.Row - 1 & ":P" & Target.Row - 1).Copy
    Range("M" & Target.Row).PasteSpecial
    
    'Make sure that if an error occurs (or not) that events 
    'are re-enabled and the sheet is re-protected.

    ERROR_OCCURRED:

    If Err.Number <> 0 Then
        MsgBox "An error occurred. Formulas may not have been copied." & vbCrLf & vbCrLf & _
            Err.Number & " - " & 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" & Target.Row).Select
    
End If

End Sub

Upvotes: 2

Greg
Greg

Reputation: 1

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.

Upvotes: 0

Jan Pingel
Jan Pingel

Reputation: 21

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 ??

Upvotes: 2

InContext
InContext

Reputation: 2501

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

Upvotes: 2

K_B
K_B

Reputation: 3678

Make the area with your data and formulas a Table:

enter image description here

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...

Upvotes: 25

Related Questions