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!
Bookmarks