G
Guest
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
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