Copy and Paste Multiple times and loop

Hello,

I have a workbook with two Sheets.
Sheet 1 has a list of Data which I want to copy into Sheet 2. Sheet 1's list is dynamic and the number of rows will always change. I want to be able to copy the data out of Sheet 1 starting from cell A2 and paste into Sheet 2 8 times so from cell A2 - A10, then move to the next one in Sheet 1 A3 copy to Sheet 2 A11 - A19.

EG.

Sheet 1

A1
Dog
Cat
Rabbit

Sheet 2
Dog
Dog
Dog
Dog
Dog
Dog
Dog
Dog
Cat
Cat
Cat
Cat
Cat
Cat
Cat
Cat
etc.

I need this code to loop through Sheet 1 till it finds a break. Only one break will be present.

Currently I am using 3 seperate codes, one which copys the data from Sheet 1 to Sheet 2 another code which inserts 7 rows under each line of data and the 3rd code copys the data however when it gets to the last row it does not know when to stop so keeps pasting down endlessly, alternatively if someone can help me with a solution in writing a code which I can let it know to stop pasting below is the code I am using.

Sub CopyRows()
'
' CopyRows Macro
'

'
StartRow = 2

Dim e As Range
For Each e In ActiveSheet.UsedRange.Rows
If Application.CountA(e) = 0 Then e.Value = e(0).Value
If Range("A242") = False Then

End If
Next e
End Sub

Thanks heaps for the help!

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(I have added them this time. Please read all our rules and abide by them in the future.)