Insert Rows Macro

[Todd]

Hi, I am trying to get a macro to insert rows and fill
formulas. I want to hit a button, have a message box open
which asks "How Many Rows To Insert" and then begin
inserting and filling formulas. Unfortunatly my eyes are
bigger than my VBA skill. I have this macro which I have
adapted before (thanks to this group).

I only want to do the insert on the active worksheet. How
do I fix it?

Thanks so much,


Todd

Sub InsertRowsAndFillFormulas(Optional vRows As Long)
' Documented:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows -- 1997/09/24 Mark Hill
<[email protected]>
'Dim vRows As Integer
' row selection based on active cell -- rev. 2000-09-02
David McRitchie
ActiveCell.EntireRow.Select 'So you do not have to
preselect entire row
If vRows <> 1 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add
Rows", _
Default:=1, Type:=1) '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 Integer
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

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

 

[Don Guillett]

This should help. Take out what you don't want.

Sub InsertRows()
usedrows = Cells(1, "b").End(xlDown).Row
x = InputBox("Enter Number of rows to insert")
y = InputBox("Enter Date")
Rows(usedrows + 1 & ":" & usedrows + x).Insert
Range(Cells(usedrows + 2, "a"), Cells(usedrows + 1 + x, "a")) = "Q " &
Format(y, "mm/dd/yy")
Range(Cells(usedrows, "H"), Cells(usedrows, "J")).Copy _
Range(Cells(usedrows + 1, "H"), Cells(usedrows + 1 + x, "J"))
End Sub

 

[David McRitchie]

Hi Todd,
For a generic solution see
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

Select the row that is to be copied down, The entire
row gets copied down and constants are removed, no
need to change the subroutine to identify which columns
have formulas. You will have to code balances or rows
with cells that refer to other rows to use OFFSET all
described on the web page.

If not familiar with macros see the top of the page, you
might also look at the toolbars.htm page for your toolbar
icon and it's installation.

 

[Todd]

Thank you both!

-----Original Message-----
This should help. Take out what you don't want.

Sub InsertRows()
usedrows = Cells(1, "b").End(xlDown).Row
x = InputBox("Enter Number of rows to insert")
y = InputBox("Enter Date")
Rows(usedrows + 1 & ":" & usedrows + x).Insert
Range(Cells(usedrows + 2, "a"), Cells(usedrows + 1 + x, "a")) = "Q " &
Format(y, "mm/dd/yy")
Range(Cells(usedrows, "H"), Cells(usedrows, "J")).Copy _
Range(Cells(usedrows + 1, "H"), Cells(usedrows + 1 + x, "J"))
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)



.

 

[Don Guillett]

glad it helped