Row 5 is where the formulas start that need to be copied. Above this
is all header info. I need the macro to insert the rows between row 4
and row 5 and then autofill the formulas in row 5 UP and then delete
the constants.



On Jun 25, 9:34 pm, FSt1 wrote:
hi
change Range("D5").Select to Range("D2").Select
or to what ever row where you want the row added.

regards
FSt1



" wrote:
I have the following code that works great (Thanks)! However I have a
need to add new students to the top of the worksheet. How can I
modify this code to add rows ABOVE the selected row (in this case row
5)?
Thanks in advance!

Sub InsertRowsAndFillFormulas()
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Insert Rows -- 1997/09/24 Mark Hill

' row selection based on active cell -- rev. 2000-09-02 David
McRitchie
Dim x As Long
Range("D5").Select
ActiveCell.EntireRow.Select 'So you do not have to preselect
entire row
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many Students 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).SelectedShee ts
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- Hide quoted text -

- Show quoted text -