Insert row + copy cell formula's error

[MeerskiD]

Hi,

I have very basic VBA knowledge (self taught).
Currently I am trying to insert a row in the range where I selected the cell.
At the moment I am stuck with following code:

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

    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

Private Sub CommandButton1_Click()
  '-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog
  Call InsertRowsAndFillFormulas
End Sub

I receive following error when I click the button:

"Run-time error '1004': AutoFill method of Range class failed"

When I push the button, a new row is inserted but then it highlights following code when I debug it:

 *Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault

I attached an image of an example how my table looks like...

excel table.PNG

within the range, the cells in the first column are merged together.
Is it possible that it has something to do with the merged cell?

Sidequestion:

Example: I select cell B3 --> push "add row" button --> new row is inserted

excel table 2.PNG (example of what the result is)

It is always under the first row that a new row is inserted. How can I change the code to insert a new row under the last row?

Thank you in advance for all the help!!

Kind regards,
MeerskiD

[patel45]

attach please xlsm sample file, not png

[MeerskiD]

I added the xmls file now, hope this helps!

[patel45]

you have some merged cells in column A, you can not select a single row with them.
eliminate column A, the your code works without errors
but you can solve with

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
   Dim x As Long
   r = ActiveCell.Row
   Range("B" & r & ":D" & r).Select
   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
   Range("B" & r + 1 & ":D" & r + vRows).Insert
   Selection.AutoFill Selection.Resize( _
     rowsize:=vRows + 1), xlFillDefault
End Sub

[MeerskiD]

patel45, thank you for your help!

Unfortunately, if I use your code, column A does not receive an additional cell. Also, the macro does not copy formula's. This also does not work in the code I gave..

Do you might know how I can fix this?

[MeerskiD]

the macro does not copy formula's.
Do you might know how I can fix this?

Nevermind, it does work! Thank you Patel45!!