Hi

I am looking for a way to insert a new row by pressing a button at the end
of the row, letting the user choose how many rows to insert, then inserting
the row(s) under the selected row and copying the format and any formulas
down, but not values.

from a code i found on a website i have built it up to the following code:


Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)


Dim x As Long

s = Application.Caller

ActiveSheet.Buttons(s).TopLeftCell.EntireRow.Selec t

If vRows = 0 Then
vRows = Application.InputBox(Prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1)
If vRows = False Then Exit Sub
End If


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).SelectedShee ts
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

x = Sheets(sht.Name).UsedRange.Rows.Count

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

Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select
End Sub

This is called from a button on the row. This works fine and does everything
i want, but the problem is i have a row of totals at the bottom which sum up
the columns and unlike when a normal 'insert row' is used and the range
specified in the sum increases to accomodate it, this is not happening with
using this code to insert a row.

any help or ideas would be much appreciated

Thankyou

Patrick