Insert Row & Copy Formula (Excel 97)

Topic

New Reply

When I insert a new row into my spreadsheet, it does not copy the formulas from the cells below. Is there a way to have it copy the formulas and insert a new row at the same time?

Reply | Quote

Replies

You could create a macro for that, but you’d have to specify exactly in which columns the formulas should be copied.

Reply | Quote

As Hans said, assuming you want to copy all the formulas and allow the references to retain their relative or absolute character:

Sub CopyInsertRow()
ActiveCell.EntireRow.Insert Shift:=xlDown
ActiveCell.EntireRow.Offset(1, 0).Copy ActiveCell.EntireRow
Application.CutCopyMode = False
End Sub

No error handling for locked cells etc.

Reply | Quote

Thanks for all the information and tutorial.

I have managed to create the macro. However I need to edit it a bit. I actually need it to insert a new row and only copy the formula of one cell (Column C). How do I do that?

Reply | Quote

The following 2 lines will insert a row where the active cell is, and copy the formula in column C:

ActiveCell.EntireRow.Insert
Range(“C” & (ActiveCell.Row – 1) & “:C” & ActiveCell.Row).FillDown

Reply | Quote

Thanks so much that worked great!

Reply | Quote

Does this do what you want:

Public Sub InsertAndCopyFormulas()
Dim oCell As Range
ActiveCell.EntireRow.Insert
For Each oCell In ActiveCell.Offset(1, 0).EntireRow.SpecialCells(xlCellTypeFormulas)
oCell.Copy Destination:=oCell.Offset(-1, 0)
Next oCell
End Sub

Reply | Quote

I’ve actually never written a macro before so I don’t really know what to do with the code that you wrote.

Reply | Quote

See Legare’s Personal.xls Tutorial (All).

Reply | Quote

In addition to what Hans said, this particular macro expects the sheet where you want the row inserted to be the active sheet. The macro will insert a row above the row with the active cell and copy the formulas from the row with the active cell to the inserted row.

Reply | Quote