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