Hello Richard,
Perhaps the following code, assigned to a button, may do the task for you:-
Sub CopyRows()
Dim r As Range
For Each r In Range("F2", Range("F" & Rows.Count).End(xlUp))
Sheet2.Cells(Rows.Count, 1).End(xlUp)(2).Resize(r.Value, 5) = r.Offset(, -5).Resize(1, 5).Value
Next r
End Sub
I've prepared a little sample at the following link for you to play with:-
https://www.dropbox.com/s/edpbkfor4j9vlay/Copy%20x%20number%20of%20rows%20to%20sht%202.xlsm?dl=0
Click on the "RUN" button to see it work.
I hope that this helps.
Cheerio,
vcoolio.