copy the row contents and based on cell value in row

chitra mr · May 12th 2016

hello everyone
i need a help in creation of macro, it should copy the row contents and based on one cell value in that row, it has to insert those many rows automatically with the contents.
lets say xxy. xxz. ere. 5
aab. err. avid. 2. ..as the number here is 5 in the first row, it should create 5 rows with the same entries in a new sheet and as 2 in second row it should create 2rows below in the new sheet

Xxy xxz. Ere 5
Xxy xxz ere 5
Xxy xxz ere 5
Xxy xxz ere 5
Xxy xxz ere 5
aab ere avid 2
Arab ere avid 2. this is how it stud appear in new sheet.hope the requirement is clear.

rollis13 · May 13th 2016

Re: copy the row contents and based on cell value in row

Supposing your data start at row 2 of Sheet1 you could have a try with this macro. Copy it in a VBA module then launch it.

Code

Option Explicit


Sub Copy_N_Times()
     
    Dim lr As Long      'last row
    Dim x As Long       'generic counter
    
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For x = 2 To lr     'from row 2 to end
         'copy and duplicate column D times in Sheet2 columns A to D (4) from Sheet1
        Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Range("D" & x).Value, 4) = Range("A" & x).Resize(, 4).Value
    Next x


End Sub

Display More

copy the row contents and based on cell value in row

Participate now!

Share