Copy/Paste row with formulas into next available row

[jhedges]

I'm using Excel 07, windows XP. The range is A4:M5 right now; however, will grow over time.

I have found this code while browsing the msg. board and added some of my own. What I'm trying to accomplish is to allow a user to press a command button to insert a new row(s). This macro would copy the data in the last row of the worksheet, including formulas, then paste the formulas in the next row(s) down and clear the row(s) (leaving the formulas) for data entry. I would also like for the cursor to end up in the first active row inserted and in the cell in column F.

It is not working correctly yet...

Sub InsertNewRows()
ActiveSheet.Unprotect Password:="aaa"
Dim RowCnt As Integer
RowCnt = Application.InputBox _
  (prompt:="How many rows do you want to insert" & _
    " starting with row " & ActiveCell.Row & "?", Type:=1)
If RowCnt = 0 Then End
Rows(ActiveCell.Row & ":" & ActiveCell.Row + RowCnt - 1).Insert _
  Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows(ActiveCell.Row - 1).Copy
Rows(ActiveCell.Row & ":" & ActiveCell.Row + RowCnt - 1). _
  PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
  SkipBlanks:=False, Transpose:=False
  Rows(ActiveCell.Row).Select
Selection.ClearContents
ActiveSheet.Protect Password:="aaa", DrawingObjects:=True, _
  Contents:=True, Scenarios:=True
End Sub

 

[jhedges]

Anyone,

I found the following code and was wondering how I could modify it to select a specific cell in the first row inserted? I want to select the cell in column E in the first row inserted from the running of the macro. I came up with the code indicated in red; however, it is not exactly what I'm looking for. This will always select E7...

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.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 Long
   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
    Range("E7").Select
    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