copying formulas when inserting new rows
 

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?

copying formulas when inserting new rows
 

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

Steve M wrote:

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

copying formulas when inserting new rows
 

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


--
Jim
"Steve M" wrote in message
...
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?

copying formulas when inserting new rows
 

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.

On Sun, 07 Sep 2008 11:24:12 -0500, Dave Peterson
wrote:

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

Steve M wrote:

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?

copying formulas when inserting new rows
 

the box you reference is checked

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

--
Jim
"Steve M" wrote in message
...
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.

On Sun, 07 Sep 2008 11:24:12 -0500, Dave Peterson
wrote:

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

Steve M wrote:

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?

copying formulas when inserting new rows
 

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 wrote:

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.

On Sun, 07 Sep 2008 11:24:12 -0500, Dave Peterson
wrote:

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

Steve M wrote:

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

copying formulas when inserting new rows
 

Jim,

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

On Sun, 7 Sep 2008 12:31:58 -0400, "Jim Rech" wrote:

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

copying formulas when inserting new rows
 

Excellent!

--
Jim
"Steve M" wrote in message
...
| Jim,
|
| Thanks for the macro. I installed it and it works great. Just what I
| needed.
|
| On Sun, 7 Sep 2008 12:31:58 -0400, "Jim Rech" wrote:
|
| 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
|