Insert row and fill down formulae

[Simon]

Hi all,

I normally don't dabble in VBA / Macros as I feel out of my depth. However
I would like to set up a "hopefully simple" macro and need your assistance.

I have a formula in column R and column S. What I would like to do is
create a macro to:
1) Insert a row from the current position - Inserting a row will never be at
the first or the last row; always somewhere in the middle
2) Copy (or fill down) the formulae from columns R & S in the row above.

I hope this makes sense.
PS - I have found threads that could theoretically help, except for the fact
that I have breaks in rows between different sets of data - for example, I
have 10 rows of data, a break of a few rows, then another 10 rows of data.
The break of a few rows are blank and should hopefully remain so.

Many thanks for your help,
Simon

 

[JLGWhiz]

This assumes the user will select a cell somewhere on the sheet for the row
to be inserted. To assign this to a button, first create the button from
the Control Tool Box,
rightclick the button, then click view code from the pop up menu. Paste the
code below into the code window.

Private Sub Commandbutton1_Click()
Rows(ActiveCell.Row).Insert
Range("R" & ActiveCell.Row - 1 & _
":S" & ActiveCell.Row - 1).Copy
Range("R" & ActiveCell.Row).PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End Sub

 

[Simon]

Many thanks, this is exactly what I needed. Most kind.