Copy one value and paste it x number of times

Hello all,

I have a workbook with two sheets, one containing customer list and another containing the products.
The number of customers and the number of products change daily.

1) I need code that would copy each customer name and paste it in a third sheet, for the same number of times as there are products - in the same column.
2) Then I need to copy and paste the products onto the third sheet, in the column next to the multiple customer name.

I have attached an example.

Can anyone PLEASE help me out as I'm going insane with trying to find a solution in the forum that would work for my scenario.

Any help much appreciated!!

Hi,

Use this loop to copy/paste. When you want to copy paste column 2, just change 1 to 2 in the code.

Please Login or Register  to view this content.

Originally Posted by KRAND

Hi,

Use this loop to copy/paste. When you want to copy paste column 2, just change 1 to 2 in the code.

Please Login or Register  to view this content.

UPS, I forgot the Loop...

Please Login or Register  to view this content.

Thats just Brilliant!! Thanks for that.
If there was a blank cell and some non product info at the bottom of the "Product" list (say 3 rows of non-product info), and I do not want this included in the combined list in "Results", would I change the last section of the code to something like:

For Each rngItem In rngCustomer.Cells
rngItem.Copy rngOutput.Resize(rngProduct.Rows.Count, 1)
rngProduct.Copy rngOutput.Offset(0, 1).Resize(rngProduct.Rows.Count-3, 1)
Set rngOutput = rngOutput.Offset(rngProduct.Rows.Count)
Next

Thanks for the advice and apologies for not tagging the code.. will remember for next time!

Much appreciated, you've saved me contless hours' work and it works beautifully!

can you post the spreadsheet with the code in it? I cant quite figure it out...

@ davidben13

It looks like you're going to be out of luck on this one. jcb hasn't posted for a year and a half.

Andy, thank you very much for this--I have never used Visual Basic, and I registered here just to see your code example. It was a huge boon for me!