Copy cell from rows to column followed by copying it a fixed number of times

[yhbyhb]

Hi,

I have data in this format:

APPLE BANANA CARROT DOG ELEPHANT FISH

And would like to make it like this:

APPLE
APPLE
APPLE
BANANA
BANANA
BANANA
CARROT
CARROT
CARROT
ELEPHANT
ELEPHANT
ELEPHANT
FISH
FISH
FISH

Should i be using
Dim i As Integer
For i = 2 To 4
Cells(i, 1).Value = Range("D1")
Next i

If so, how do i change the row to make sure it starts copying a new cell after every 3 rows? And, how do i change the range "D1" to the next cell after i finish 3 copying it for 3 rows?

Thanks for helping!

 

[Momentman]

Welcome to the forum

In what cells do you have the original data and what cells(row/column) do you want to copy them to?

 

[yhbyhb]

I have it in this format:

A B C D E F G H
1 Name Type Colour Apple Banana Carrot Elephant Fish
2
3
4

And would like to make it to this:

A B C D E F G H
1 Name Type Colour
2 Apple
3 Apple
4 Apple
5 Banana
6 Banana
7 Banana
8 Carrot
9 Carrot
10 Carrot
11 Elephant
12 Elephant
13 Elephant
14 Fish
15 Fish
16 Fish

How should i go about doing it? Thanks for the reply!

 

[Momentman]

Something like this

Sub codecopy()
    Dim lrow As Integer, i As Integer, j As Integer
    lrow = 2
    For j = 4 To 8 'Apple is in column 4 all the way to Fish in col 8
        For i = 1 To 3 'bcos you want to copy it 3 times
            Range("A" & lrow) = Cells(1, j)
            lrow = lrow + 1
        Next i
    Next j
End Sub

 

[yhbyhb]

It works perfectly! Thanks!