# Excel - Copy rows "n" times with calculation

Solved/Closed
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
I need to have a macro that will copy rows from sheet1 to sheet 2 . The catch is from sheet 1 col C for e.g. C2 may have the number 5 in it. I need to copy that row and paste it 5 times on sheet 2 with some calculations. Let's see below:

Sheet 1
Col A Col B Col C
Row 1 Name Place Month
Row 2 ABC 20 5
Row 3 DEF 14 3

Sheet 2
Col A Col B formula Note
Row 1 Name FTE
Row 2 ABC 4 =rounddown(20/5,2)
Row 3 ABC 4 =rounddown(20/5,2)
Row 4 ABC 4 =rounddown(20/5,2)
Row 5 ABC 4 =rounddown(20/5,2)
Row 6 ABC 4 =20 - sum(B1:B4) Remaining Balance
Row 7 DEF 4.66 =rounddown(14/3,2)
Row 8 DEF 4.66 =rounddown(14/3,2)
Row 9 DEF 4.68 =14 - sum(B6:B7) Remaining Balance

## 2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
```Sub CopyData()
Dim lRow As Long
Dim RepeatFactor As Variant
Dim vNumber As Variant
Dim vDivFactor As Variant

Sheets("Sheet1").UsedRange.Copy

Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste

lRow = 1
Do While (Cells(lRow, "A") <> "")

RepeatFactor = Cells(lRow, "C")
If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then

Range(Cells(lRow, "A"), Cells(lRow, "C")).Copy
Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "C")).Select
Selection.Insert Shift:=xlDown

vNumber = Cells(lRow, "B")
vDivFactor = Cells(lRow, "C")

If vNumber = "" Then vNumber = 0

If ((vDivFactor <> "") And (vDivFactor <> 0) And (IsNumeric(vDivFactor)) And (IsNumeric(vNumber))) Then

Range(Cells(lRow, "B"), Cells(lRow + RepeatFactor - 2, "B")).Value = Round(vNumber / vDivFactor, 2)

Cells(lRow + RepeatFactor - 1, "B").Value = vNumber - (Round(vNumber / vDivFactor, 2) * (vDivFactor - 1))

Range(Cells(lRow, "C"), Cells(lRow + RepeatFactor - 1, "C")).Clear

End If

lRow = lRow + RepeatFactor - 1

End If

lRow = lRow + 1
Loop
End Sub```
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Try this

```Sub CopyData()
Dim lRow As Long
Dim RepeatFactor As Variant
Dim vNumber As Variant
Dim vDivFactor As Variant

Sheets("Sheet1").UsedRange.Copy

Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste

lRow = 1
Do While (Cells(lRow, "A") <> "")

RepeatFactor = Cells(lRow, "C")
If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then

Range(Cells(lRow, "A"), Cells(lRow, "C")).Copy
Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor, "C")).Select
Selection.Insert Shift:=xlDown

vNumber = Cells(lRow, "B")
vDivFactor = Cells(lRow, "C")

If vNumber = "" Then vNumber = 0

If ((vDivFactor <> "") And (vDivFactor <> 0) And (IsNumeric(vDivFactor)) And (IsNumeric(vNumber))) Then

Range(Cells(lRow, "B"), Cells(lRow + RepeatFactor - 1, "B")).Value = Round(vNumber / vDivFactor, 2)

Cells(lRow + RepeatFactor, "B").Value = vNumber - WorksheetFunction.Sum(Range(Cells(lRow, "B"), Cells(lRow + RepeatFactor - 1, "B")))

Range(Cells(lRow, "C"), Cells(lRow + RepeatFactor, "C")).Clear

End If

lRow = lRow + RepeatFactor - 1

End If

lRow = lRow + 1
Loop
End Sub```
Posts
3
Registration date
Friday May 14, 2010
Status
Member
Last seen
May 18, 2010

Thanks for your reply. I want to add some details for my request. If in sheet 1 col A is "ABC", col B value is 5 and col C value is 3 (col C mean the row number will be copy).

Then Sheet 2 result will be:
ABC 1.66
ABC 1.66
ABC 1.68