Copy once and paste five times
 

Hi

I have a column of 172 text values in Sheet2. I want to paste each
value five times in the first column of Sheet1. For example:

--------------
Sheet2:

Column A

Biscuits
Jam
Peanut
Chocolate
....


--------------
Sheet1:

Column B

Biscuits
Biscuits
Biscuits
Biscuits
Biscuits
Jam
Jam
Jam
Jam
Jam
Peanut
Peanut
Peanut
Peanut
Peanut
Chocolate
Chocolate
Chocolate
Chocolate
Chocolate
....

So far I have coded the following:

Sub Macro1()

Dim i
Dim j

For i = 1 To Sheet2.UsedRange.Rows.Count

For j = 1 To 5

Sheets(1).Range("A" & j) = Sheets(2).Range("A" &
i).Value

Next

Next


End Sub

As you can see, the above would simply paste the values over and over
again on the first five cells. I am not sure how to get around this
problem.

I would be grateful for any help.

Regards
Yousaf

Copy once and paste five times
 

Sheets(1).Range("A" & (j + (i - 1) * 5)) = Sheets(2).Range("A" & i).Value
This is all on one line, of course

"Zuzeppeddu" wrote in message
ps.com...
Hi

I have a column of 172 text values in Sheet2. I want to paste each
value five times in the first column of Sheet1. For example:

--------------
Sheet2:

Column A

Biscuits
Jam
Peanut
Chocolate
...


--------------
Sheet1:

Column B

Biscuits
Biscuits
Biscuits
Biscuits
Biscuits
Jam
Jam
Jam
Jam
Jam
Peanut
Peanut
Peanut
Peanut
Peanut
Chocolate
Chocolate
Chocolate
Chocolate
Chocolate
...

So far I have coded the following:

Sub Macro1()

Dim i
Dim j

For i = 1 To Sheet2.UsedRange.Rows.Count

For j = 1 To 5

Sheets(1).Range("A" & j) = Sheets(2).Range("A" &
i).Value

Next

Next


End Sub

As you can see, the above would simply paste the values over and over
again on the first five cells. I am not sure how to get around this
problem.

I would be grateful for any help.

Regards
Yousaf

Copy once and paste five times
 

Thanks. Works perfectly.