How do I add rows to a spreadsheet and keep t

DerbyDad03 November 18, 2010 at 20:07:09

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 McRitchie Sub 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 Sub This macro will insert a row and keep the formula used in the previous row. Cheers!