How to create duplicae rows

[Jeff73]

I am trying to create a macro that will copy and duplicate five multiple rows below that one or in another sheet


Example:

Original Data

A001
A002
A003



Data Needed

A001
A001
A001
A001
A001
A002
A002
A002
A002
A002
A003
A003
A003
A003
A003

Any help anyone may be able to give would be very helpful. Thanks in advance

[tigeravatar]

Jeff73,

Welcome to the forum. You can use the following code. Just select what you want to make duplicates of, and the code will duplicate each item 4 times (so you'll end up with 5 total per item, including the original):

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[martindwilson]

formula approach
assuming data starts in sheet1 a1
=INDEX(Sheet1!A:A,CEILING(ROW(A1)/5,1)) dragged down

[Jeff73]

Thanks for the quick response and the help!! I just noticed it was my first post and I messed up the spelling in the title... lol

The macro seems to work fine but I guess I should have mentioned it but I have data in row B, C,D, and so on that I need to duplicate and carry along with that data. I hope that makes sense.

Thanks in advance for the help

[martindwilson]

formula will do that drag it across then down

[tigeravatar]

Jeff73,

The macro seems to work fine but I guess I should have mentioned it but I have data in row B, C,D, and so on that I need to duplicate and carry along with that data

The macro already does that:

Just select what you want to make duplicates of

So you just need to select the range you want, then run the macro. Martin's solution also works if you already have free rows.

~tigeravatar

[Jeff73]

tigeravatar

martindwilson,

Thanks for all your help! I ended up using the macro and it works great