Excel - Copy rows "n" times with calculation

Solved/Closed
Tracy - May 13, 2010 at 04:42 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 14, 2010 at 04:02 AM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
May 14, 2010 at 04:02 AM
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
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
May 13, 2010 at 08:11 AM
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
0
mama_tracy Posts 3 Registration date Friday May 14, 2010 Status Member Last seen May 18, 2010
May 14, 2010 at 02:50 AM
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!!!
0