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

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)

"Patrick Bateman" wrote in
message ...
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

for example

=SUM(J21:J97)

Regards

Patrick

"Bob Phillips" wrote:

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)

"Patrick Bateman" wrote in
message ...
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

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)

"Patrick Bateman" wrote in
message ...
for example

=SUM(J21:J97)

Regards

Patrick

"Bob Phillips" wrote:

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)

"Patrick Bateman" wrote in
message ...
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