Previous Topic Next Topic

Excel - Copy rows "n" times with calculation

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


Related:

2 responses

Réponse 1 / 2
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
14 May 2010 à 04:02 
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
Réponse 2 / 2
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
13 May 2010 à 08:11
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
mama_tracy Posts 3 Registration date Friday 14 May 2010 Status Member Last seen 18 May 2010
14 May 2010 à 02:50
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!!!

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Copying data from one Excel sheet to another.
Remedial -
Kav -

30 responses