How do I add rows to a spreadsheet so that the existing formulas copy into the new rows? I want the emply cells to have the formulas in them, so that when I begint o populate, the calculations are automatic.
Right Click the Row Number(s) of the Row(s) with the formulas you want and choose Copy. Right Click the Row Number where you want the Row(s) inserted and choose Insert Copied Cells
The Row(s) will be Inserted/Pasted above the row you choose.
Posting Tip: Before posting Data or VBA Code, read this How-To.
Hi there!
You can use this macro that was written By David McRitchieSub InsertRowsAndFillFormulas(Optional vRows As Long = 0) ' Documented: <a href="http://www.mvps.org/dmcritchie/excel/insrtrow.htm" target="_blank">http://www.mvps.org/dmcritchie/exce...</a> ' 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.Select 'So you do not have to preselect entire row If vRows = 0 Then vRows = Application.InputBox(prompt:= _ "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 Integer ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _ Windows(1).SelectedSheets.Count) i = 0 For Each sht In _ Application.ActiveWorkbook.Windows(1).SelectedSheets Sheets(sht.Name).Select i = i + 1 shts(i) = sht.Name x = Sheets(sht.Name).UsedRange.Rows.Count 'lastcell fixup Selection.Resize(rowsize:=2).Rows(2).EntireRow. _ Resize(rowsize:=vRows).Insert 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).EntireRow. _ SpecialCells(xlConstants).ClearContents Next sht Worksheets(shts).Select End SubThis macro will insert a row and keep the formula used in the previous row.
Cheers!