copying formulas when inserting new rows

[Steve M]

Is it possible to automatically copy formulas from other rows to new
rows when adding a new line in a spreadsheet? I have cols A-AE of
which cols A-N are inputted and the others are calcualated values from
the data. How can I copy these formulas to the new cells when I add a
new row of data? Currently I have several 'dummy' rows that contain
the formulas that are used to input the data. Then I have to sort the
worksheet to get the new rows inserted properly. I'd like to insert a
new row at the proper location, enter the data and have the formulas
copied to the new row. Any ideas?

 

[Dave Peterson]

You may want to try this:
Tools|Options|Edit Tab|check "extend data range formats and formulas"

Or you could use a macro...

David McRitchie shares some code:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
look for: InsertRowsAndFillFormulas

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Jim Rech]

Dave mentioned the extend data feature and McRitchie site but since I find
entend data a little finicky and because I wrote a macro prior to Seeing
Dave's reply, I'm posting it<g>:

Sub Macro1()
With Intersect(ActiveCell.CurrentRegion, ActiveCell.EntireRow)
.Insert xlDown
With .Offset(-1)
.FillDown
.SpecialCells(xlCellTypeConstants).ClearContents
End With
End With
End Sub

 

[Steve M]

Dave,
I checked my options and the box you reference is checked and yet when
I add a new row, the cells are not populated with any formulas.

 

[Jim Rech]

the box you reference is checked

So now you know what I meant by finicky if per chance you saw my message.

 

[Dave Peterson]

I agree with Jim. In fact, I've turned this off because I could never
understand how excel was going to "help" me.

And instead of fighting it, I just figured it would be easier for me to do the
work myself.

 

[Steve M]

Jim,

Thanks for the macro. I installed it and it works great. Just what I
needed.

 

[Jim Rech]

Excellent!

--
Jim
| Jim,
|
| Thanks for the macro. I installed it and it works great. Just what I
| needed.
|
|
| >Dave mentioned the extend data feature and McRitchie site but since I
find
| >entend data a little finicky and because I wrote a macro prior to Seeing
| >Dave's reply, I'm posting it<g>:
| >
| >Sub Macro1()
| > With Intersect(ActiveCell.CurrentRegion, ActiveCell.EntireRow)
| > .Insert xlDown
| > With .Offset(-1)
| > .FillDown
| > .SpecialCells(xlCellTypeConstants).ClearContents
| > End With
| > End With
| >End Sub
|