Excel - Copy rows "n" times with calculation [Solved/Closed]

Report
-
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
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
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
757
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 4223 users have said thank you to us this month

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
757
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

However, your macro result is:
ABC 1.66
ABC 1.66
ABC 1.66
ABC 0.02

In any case, many thanks for your help!!!