Macro to copy formulas to newly added row automatically

[senthia87]

How do you auto copy formulas from row above to a newly added row?

 

[Joe4]

With VBA, most likely.

If you tell us how a new row is added (are you inserting a blank row, or making an entry into a specific column) and what columns these formulas are in, we can help you create that VBA code.

 

[Smitty]

Welcome to the Board!

If you format the data range as a Table, then Excel will automatically extend formulas for you as soon as you start adding data to a new row.

HTH,

 

[Jeffrey Lopez]

Here is a quick snippet I wrote up, it is a Worksheet change even, so it must be entered into the Worksheet Code. You can do this by Right Clicking on the Tab/Sheet name > View Code and Paste it there.


Since you did not tell us what columns you wanted to bring down each time, I figured it would be more than, because if it was just one, a simple copy paste would suffice...

So first we have an array, this can be scaled to as many columns as you like, in my example I just used 3 (Columns: 3,6,9)

Dim lngCols(1 To 3) As Long


lngCols(1) = 3
lngCols(2) = 6
lngCols(3) = 9

Next we are going to check to see if a row was inserted...

        If Target.Columns.Count <> Columns.Count Then Exit Sub

If not, it exits the sub, it does, we are going to do a simple loop though the columns we defined above...

            For i = 1 To UBound(lngCols)

And then we just copy, select paste and done...

                Cells(Target.Offset(-1, 0).Row, lngCols(i)).Copy
                Cells(Target.Row, lngCols(i)).Select
                ActiveCell.PasteSpecial xlPasteFormulas

And that is pretty much it, if you have any questions or run into any issues, just let me know, and please be sure to back up your excel workbook before running this macro!

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

Dim lngCols(1 To 3) As Long

lngCols(1) = 3
lngCols(2) = 6
lngCols(3) = 9

    On Error GoTo ErrorHandler:
        If Target.Columns.Count <> Columns.Count Then Exit Sub
            
            For i = 1 To UBound(lngCols)
                Cells(Target.Offset(-1, 0).Row, lngCols(i)).Copy
                Cells(Target.Row, lngCols(i)).Select
                ActiveCell.PasteSpecial xlPasteFormulas
            Next i

ErrorHandler:
Application.ScreenUpdating = True
End Sub

 

[pmacp]

This is a life saver, thank you! Our requirements have one added twist. We manually insert multiple rows (some cells have data and some have formulas). The code below copies formula to only row. Is it possible to copy formulas from the row above into all rows that are newly inserted? many thanks

Here is a quick snippet I wrote up, it is a Worksheet change even, so it must be entered into the Worksheet Code. You can do this by Right Clicking on the Tab/Sheet name > View Code and Paste it there.


Since you did not tell us what columns you wanted to bring down each time, I figured it would be more than, because if it was just one, a simple copy paste would suffice...

So first we have an array, this can be scaled to as many columns as you like, in my example I just used 3 (Columns: 3,6,9)

Dim lngCols(1 To 3) As Long


lngCols(1) = 3
lngCols(2) = 6
lngCols(3) = 9

Next we are going to check to see if a row was inserted...

        If Target.Columns.Count <> Columns.Count Then Exit Sub

If not, it exits the sub, it does, we are going to do a simple loop though the columns we defined above...

            For i = 1 To UBound(lngCols)

And then we just copy, select paste and done...

                Cells(Target.Offset(-1, 0).Row, lngCols(i)).Copy
                Cells(Target.Row, lngCols(i)).Select
                ActiveCell.PasteSpecial xlPasteFormulas

And that is pretty much it, if you have any questions or run into any issues, just let me know, and please be sure to back up your excel workbook before running this macro!

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

Dim lngCols(1 To 3) As Long

lngCols(1) = 3
lngCols(2) = 6
lngCols(3) = 9

    On Error GoTo ErrorHandler:
        If Target.Columns.Count <> Columns.Count Then Exit Sub
            
            For i = 1 To UBound(lngCols)
                Cells(Target.Offset(-1, 0).Row, lngCols(i)).Copy
                Cells(Target.Row, lngCols(i)).Select
                ActiveCell.PasteSpecial xlPasteFormulas
            Next i

ErrorHandler:
Application.ScreenUpdating = True
End Sub