Inserting Row

P

Patrick Bateman

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.Select

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).SelectedSheets
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
 
B

Bob Phillips

What formula are you using in the totals row, it should expand just the
same.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
P

Patrick Bateman

for example

=SUM(J21:J97)

Regards

Patrick

Bob Phillips said:
What formula are you using in the totals row, it should expand just the
same.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
B

Bob Phillips

That should work okay Patrick. If you inserted say 2 rows at row 50, that
will change to =SUM(J21:J99). What does yours say?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top