I have a code that inserts a number of rows based on a number that is put in
on my spread sheet and it works beautifully thanks to Martin Fishlock,
however, I would like to have the formula from the rows above to be auto
filled in the newly inserted rows. Any help would be greatly appreciated.

I am pasting my original question as well as Martin's reply.

Original question:

I have created a group insurance rate quote sheet and ideally I would like to
> have a cell where I can put the number of subscribers and then run a macro
> that will insert that number of rows in 6 different locations on the same
> worksheet and to copy the formulas and formatting.
>
> To help visualize the worksheet: starting with cell C15:I15 is where you
> enter the 1st subscriber's information that will determine their rate. Then
> under that there are 5 different plans or options with the Plan 1 data range
> at c18:m22, Plan 2's range is from c24:m28 and the rest of the plans follow
> suit. So, for example, if I have a group of 10 people, I put in the value of
> 10 in lets say cell b1, the macro then inserts 10 rows where I enter the
> subscriber's information and then adds 10 rows to each of the 5 plans and
> keeps the formatting and formulas for all. Is this possible?

Martin's reply:

This will insert rows at the places given. It does not deal with any
formatting as not sure what formating or formulas.

Butr it should get you started.

Sub doinsertrows()
Dim rowstoinsertat As Variant
Dim rowstoinsertnr As Long
Dim r As Long
Dim c As Long

rowstoinsertnr = ActiveSheet.Range("B1") ' number of rows
rowstoinsertat = Array(16, 23, 29, 35, 41, 47) ' where to insert rows

For r = UBound(rowstoinsertat) To LBound(rowstoinsertat) Step -1
For c = 1 To rowstoinsertnr
ActiveSheet.rows(rowstoinsertat(r)).Insert
Next c
Next r
End Sub



--
Hope this helps
Martin Fishlock

As mentioned above, the code that Martin provided works great for inserting
the rows a the different locations, but I still need help in having the newly
inserted rows to auto fill with the formulas.

Thanks!


--
D Shearer

Hi again, I picked up your post and suggest the following:

ActiveSheet.Rows(rowstoinsertat(r) - 1).Copy
ActiveSheet.Rows(rowstoinsertat(r))

This copies the whole row. if you just want a cell change the selection to

ActiveSheet.range("C" & rowstoinsertat(r) - 1).Copy
ActiveSheet.range("C" & rowstoinsertat(r))

you also may need to do formating on the cell and this is achieved with

ActiveSheet.Range("C" & rowstoinsertat(r)).Borders.LineStyle
= xlContinuous

for a single line border and you put that after the copy statement.

Sub doinsertrows()
Dim rowstoinsertat As Variant
Dim rowstoinsertnr As Long
Dim r As Long
Dim c As Long

rowstoinsertnr = ActiveSheet.Range("B1") ' number of rows
rowstoinsertat = Array(16, 23, 29, 35, 41, 47) ' where to insert rows

For r = UBound(rowstoinsertat) To LBound(rowstoinsertat) Step -1
For c = 1 To rowstoinsertnr
ActiveSheet.Rows(rowstoinsertat(r)).Insert
ActiveSheet.Rows(rowstoinsertat(r) - 1).Copy
ActiveSheet.Rows(rowstoinsertat(r))
Next c
Next r
End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"dwshearer" wrote:

> I have a code that inserts a number of rows based on a number that is put in
> on my spread sheet and it works beautifully thanks to Martin Fishlock,
> however, I would like to have the formula from the rows above to be auto
> filled in the newly inserted rows. Any help would be greatly appreciated.
>
> I am pasting my original question as well as Martin's reply.
>
> Original question:
>
> I have created a group insurance rate quote sheet and ideally I would like to
> > have a cell where I can put the number of subscribers and then run a macro
> > that will insert that number of rows in 6 different locations on the same
> > worksheet and to copy the formulas and formatting.
> >
> > To help visualize the worksheet: starting with cell C15:I15 is where you
> > enter the 1st subscriber's information that will determine their rate. Then
> > under that there are 5 different plans or options with the Plan 1 data range
> > at c18:m22, Plan 2's range is from c24:m28 and the rest of the plans follow
> > suit. So, for example, if I have a group of 10 people, I put in the value of
> > 10 in lets say cell b1, the macro then inserts 10 rows where I enter the
> > subscriber's information and then adds 10 rows to each of the 5 plans and
> > keeps the formatting and formulas for all. Is this possible?
>
> Martin's reply:
>
> This will insert rows at the places given. It does not deal with any
> formatting as not sure what formating or formulas.
>
> Butr it should get you started.
>
> Sub doinsertrows()
> Dim rowstoinsertat As Variant
> Dim rowstoinsertnr As Long
> Dim r As Long
> Dim c As Long
>
> rowstoinsertnr = ActiveSheet.Range("B1") ' number of rows
> rowstoinsertat = Array(16, 23, 29, 35, 41, 47) ' where to insert rows
>
> For r = UBound(rowstoinsertat) To LBound(rowstoinsertat) Step -1
> For c = 1 To rowstoinsertnr
> ActiveSheet.rows(rowstoinsertat(r)).Insert
> Next c
> Next r
> End Sub
>
>
>
> --
> Hope this helps
> Martin Fishlock
>
> As mentioned above, the code that Martin provided works great for inserting
> the rows a the different locations, but I still need help in having the newly
> inserted rows to auto fill with the formulas.
>
> Thanks!
>
>
> --
> D Shearer