Results 1 to 10 of 10

20050620, 16:54 #1
 Join Date
 Aug 2004
 Posts
 57
 Thanks
 0
 Thanked 0 Times in 0 Posts
Insert Row & Copy Formula (Excel 97)
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?

20050620, 16:57 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Insert Row & Copy Formula (Excel 97)
You could create a macro for that, but you'd have to specify exactly in which columns the formulas should be copied.

20050620, 17:18 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Insert Row & Copy Formula (Excel 97)
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.John ... I float in liquid gardens
UTC 7ąDS

20050620, 17:50 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Insert Row & Copy Formula (Excel 97)
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 SubLegare Coleman

20050620, 21:05 #5
 Join Date
 Aug 2004
 Posts
 57
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Insert Row & Copy Formula (Excel 97)
I've actually never written a macro before so I don't really know what to do with the code that you wrote. <img src=/S/help.gif border=0 alt=help width=23 height=15>

20050620, 21:19 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Insert Row & Copy Formula (Excel 97)
See Legare's <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post>.

20050620, 21:56 #7
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Insert Row & Copy Formula (Excel 97)
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.
Legare Coleman

20050627, 19:19 #8
 Join Date
 Aug 2004
 Posts
 57
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Insert Row & Copy Formula (Excel 97)
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?

20050627, 19:30 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Insert Row & Copy Formula (Excel 97)
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

20050627, 19:34 #10
 Join Date
 Aug 2004
 Posts
 57
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Insert Row & Copy Formula (Excel 97)
Thanks so much that worked great!