Copy & Paste Data a Specific Number of Times Question!

[abl5037]

Hello all,

I am relativly new to VBA, and I have spent a fair amount of time trying to figure out the following, however it appears that I am stuck!

The situation:

In one worksheet (sheet 1) I have a column of numbers
ex:

3
2
5

In another worksheet (sheet 2) I have a matrix of data that has the same number of rows as in sheet 1.
ex:

C678 2550 25 811 0
C563 2345 30 815 0
R456 5345 50 915 0

I am wanting to paste each row of sheet 2 into another sheet (sheet 3) X number of times (where X is equal to the corresponding value in sheet 1)
ex:

C678 2550 25 811 0
C678 2550 25 811 0
C678 2550 25 811 0
C563 2345 30 815 0
C563 2345 30 815 0
R456 5345 50 915 0
R456 5345 50 915 0
R456 5345 50 915 0
R456 5345 50 915 0
R456 5345 50 915 0


Can someone help me do this?! I have been trying and have not been able to get it to work at all! Thank you very much! If you need anymore information please let me know. Any help is greatly appreciated!

Thanks,

Austin

 

[John_w]

Try this. I've assumed that the column of numbers on sheet 1 starts in cell A1. Change the code which refers to A, A1, etc., as appropriate if this isn't the case.

Sub Copy_Rows()

    Dim lastRow As Long
    Dim Sheet3Row As Long
    Dim cell As Range
    
    With Sheets("Sheet1")
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Sheet3Row = 1
        For Each cell In .Range("A1:A" & lastRow)
            Sheets("Sheet2").Rows(cell.Row).Copy Sheets("Sheet3").Rows(Sheet3Row & ":" & Sheet3Row + cell.Value - 1)
            Sheet3Row = Sheet3Row + cell.Value
        Next
    End With
    
End Sub