Copying formulas with Macro
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 |
Copying formulas with Macro
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 |
All times are GMT +1. The time now is 02:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com