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

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

"Zuzeppeddu" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

Thanks. Works perfectly.