Copy rows in excel multiple "n" times

[nefertitinka]

Dear all,

I have data in rows, which I have to paste in another files "n" times one after another. "n" is shown in column 1 and equals the required number of copies of the row.

The row data looks like this:
3 company1 share price1
7 company2 share price2
5 company3 share price3
2 company4 share price4
4 company5 share price5

The output should look like this:
3 company1 share price1
3 company1 share price1
3 company1 share price1
7 company2 share price2
7 company2 share price2
7 company2 share price2
7 company2 share price2
7 company2 share price2
7 company2 share price2
7 company2 share price2
5 company3 share price3
5 company3 share price3
5 company3 share price3
5 company3 share price3
5 company3 share price3
2 company4 share price4
2 company4 share price4
4 company5 share price5
4 company5 share price5
4 company5 share price5
4 company5 share price5

Please, help. I have tens of thousands of such inputs and it is not possible to do it manually.

 

[Weaver]

Welcome to the board

Try the following code - it takes data from the active sheet and duplicates it into a newly created sheet.

 Sub duplicate()
    Set src = ActiveSheet
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    nr = 1
    Set dst = Sheets.Add
    For i = 1 To lr
        c = Val(src.Cells(i, 1))
        If c = 0 Then c = 1
        src.Rows(i).Copy dst.Rows(nr).Resize(c)
        nr = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Next
End Sub

HTH

PS I hope you're using XL 2007 or later if you have so many inputs - there's no error checking for reaching the last available row, possible when you only have 65,000 available, not likely with over a million. If available space is likely to be an issue, let me know.

 

[nefertitinka]

AWESOME, Thank you so much! Works perfectly! The job was done in a minute, you are a wizard

 

[bruceb_uk]

I registered JUST so I could say thanks Weaver.

Good work

thanks

Bruce

 

[indian4603]

Welcome to the board

Try the following code - it takes data from the active sheet and duplicates it into a newly created sheet.

 Sub duplicate()
    Set src = ActiveSheet
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    nr = 1
    Set dst = Sheets.Add
    For i = 1 To lr
        c = Val(src.Cells(i, 1))
        If c = 0 Then c = 1
        src.Rows(i).Copy dst.Rows(nr).Resize(c)
        nr = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Next
End Sub

HTH

PS I hope you're using XL 2007 or later if you have so many inputs - there's no error checking for reaching the last available row, possible when you only have 65,000 available, not likely with over a million. If available space is likely to be an issue, let me know.

This really works like magic.....Thank you very much for this solution

 

[Legacy 460913]

Hi, I need to copy 47 rows 367 times in Excel 2007. I used your code and modified it but it produces only a new sheet with no data (does the same thing without modifications). Any ideas. Thanks in advance.

 

[jolivanes]

@kavlito.
You should start your own thread and not hijack someone else's thread.
But why would you count the rows in column 47?
I have not checked your code but see what happens if you change the lr line to 1 where it has 47 now.

 

[Legacy 460913]

Excuse me 'Well-known Member'. I was just replying for advice. No need to use 'terroristic' threats.

 

[jolivanes]

I guess you got advice. Start your own!

But since you had such a good comeback!
Seriously though, that should not be done. You can always refer to this thread with a hyperlink
Assumes you want to copy/paste up to column 9 (=Column I)

Sub Try()
    'Thanks to Fluff
    Range("A1:I47").Copy Range("A48").Resize(47 * 367, 9)    '<---- Change the "I" and 9 as required
End Sub