iquagmire
asked on
How do I Insert rows in excel while maintaining formulas from previous rows? Is there a way to Insert but copy previous row's formulas?
We're using Excel 2003 and I have a simple budget sheet.
When my users enter their numbers, they sometimes insert their own rows (even though there are hundreds of rows available).
When this happens, the formulas get messed up. Is there an easy way to have the previous row copied when a row is inserted?
If possible, no VBA answers as I don't know VBA (unless you can provide the code - which would be greatly appreciated)
Thank you for any information you can provide. I have a budget meeting in 3 hours - Please help!
When my users enter their numbers, they sometimes insert their own rows (even though there are hundreds of rows available).
When this happens, the formulas get messed up. Is there an easy way to have the previous row copied when a row is inserted?
If possible, no VBA answers as I don't know VBA (unless you can provide the code - which would be greatly appreciated)
Thank you for any information you can provide. I have a budget meeting in 3 hours - Please help!
ASKER
This is good Kevin. I will add that.
But, when a row is inserted it does not copy anything at all from the previous rows (or the one after it). This causes a problem where I have 80 rows and someone is in line 20 and inserts a row and the cells in the new rows have no formula at all.
When a row is inserted, I need the previous formula to get copied into it so my bottom line is intact.
But, when a row is inserted it does not copy anything at all from the previous rows (or the one after it). This causes a problem where I have 80 rows and someone is in line 20 and inserts a row and the cells in the new rows have no formula at all.
When a row is inserted, I need the previous formula to get copied into it so my bottom line is intact.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is great! How can I do this?
Dear iquagmire,
Would it not be easier to simply protect your spreadsheet against the inserting and deleting of rows and columns?
http://office.microsoft.com/en-us/excel/HP052388541033.aspx
Would it not be easier to simply protect your spreadsheet against the inserting and deleting of rows and columns?
http://office.microsoft.com/en-us/excel/HP052388541033.aspx
ASKER
I actually WANT them to insert rows but I need the formulas to come with it.
ASKER
I found the answer. But Zorvek came closest. Thanks for the help!
http://www.mvps.org/dmcritchie/excel/insrtrow.htm#offset
Here's the code I copied and pasted:
Sub InsertRowsAndFillFormulas_ caller()
'-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog
Call InsertRowsAndFillFormulas
End Sub
Sub InsertRowsAndFillFormulas( Optional vRows As Long = 0)
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows -- 1997/09/24 Mark Hill <markhill@charm.net.noSpam >
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
Dim x as long
ActiveCell.EntireRow.Selec t 'So you do not have to preselect entire row
If vRows = 0 Then
vRows = Application.InputBox(promp t:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If
'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line
'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
Dim sht As Worksheet, shts() As String, i As Long
ReDim shts(1 To Worksheets.Application.Act iveWorkboo k. _
Windows(1).SelectedSheets. Count)
i = 0
For Each sht In _
Application.ActiveWorkbook .Windows(1 ).Selected Sheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name
x = Sheets(sht.name).UsedRange .Rows.Coun t 'lastcell fixup
Selection.Resize(rowsize:= 2).Rows(2) .EntireRow . _
Resize(rowsize:=vRows).Ins ert Shift:=xlDown
Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault
On Error Resume Next 'to handle no constants in range -- John McKee 2000/02/01
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize (vRows).En tireRow. _
SpecialCells(xlConstants). ClearConte nts
Next sht
Worksheets(shts).Select
End Sub
http://www.mvps.org/dmcritchie/excel/insrtrow.htm#offset
Here's the code I copied and pasted:
Sub InsertRowsAndFillFormulas_
'-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog
Call InsertRowsAndFillFormulas
End Sub
Sub InsertRowsAndFillFormulas(
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows -- 1997/09/24 Mark Hill <markhill@charm.net.noSpam
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
Dim x as long
ActiveCell.EntireRow.Selec
If vRows = 0 Then
vRows = Application.InputBox(promp
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If
'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line
'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
Dim sht As Worksheet, shts() As String, i As Long
ReDim shts(1 To Worksheets.Application.Act
Windows(1).SelectedSheets.
i = 0
For Each sht In _
Application.ActiveWorkbook
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name
x = Sheets(sht.name).UsedRange
Selection.Resize(rowsize:=
Resize(rowsize:=vRows).Ins
Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault
On Error Resume Next 'to handle no constants in range -- John McKee 2000/02/01
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize
SpecialCells(xlConstants).
Next sht
Worksheets(shts).Select
End Sub
The most obvious solution to this problem is to not reference specific rows above or below the row in which the formula resides. If this is not possible then use the OFFSET function. For example, if a formula in row 6 references a cell in row 5, column A, replace the reference A5 with OFFSET(A6,-1,0). When formulas are constructed this way Excel will always leave the cell references alone when inserting and deleting rows.
Kevin