using vb for copying and inserting rows and formulae

[katie]

I earlier posted the followin message:

I have tried to create a macro that will insert a new row
into a user specified position on worksheet 1, and then
the macro then inserts a new row into the same row number
on worksheets 2-6
So if i have highlighted row 5 on worksheet 1 and then run
the macro, a new row is inserted on worksheet 1 and then
also the same happens, in the same position, on the
following 5 worksheets.

My problem arises in the fact that i want the formulae i
have in the cells above(or below - it makes no difference)
the new inserted row to be copied into the new row i have
just inserted.
Any ideas?
Many thanks.

To which i received a reply which led me to this website
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

unfortunately it seems to be VB based, and i have no
experience with VB and i dont really know where to start,
i have tried to put someting together and this is below,
but when i click the button - to which i have assigned
this cose, a compile error pops up on screen. If anyone
could help i would be very thankful.



Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
Dim x as long
ActiveCell.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 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
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

 

[Bob Phillips]

Katie,

Try this as a starter

Sub InsertRowsAndFillFormulas()
Dim x
x = InputBox( _
prompt:="How many rows do you want to add?", _
Title:="Add Rows")
If x = "" Or x < 1 Then
Exit Sub
Else
ActiveCell.Resize(x, 1).EntireRow.Insert
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)