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

"Todd" wrote in
message
...
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
' Insert Rows -- 1997/09/24 Mark Hill

'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





.