I would look at other method for this other than a straight loop, as it would be quite slow.
If you follow this link: http://www.ozgrid.com/VBA/VBALoops.htm you will see a Procedure called "NAtoZero". This uses the Find Method with the COUNTIF function in VBA to loop through ONLy as many times as needed. The Find Method is also about 10000% faster than a loop.
The code could be modified to something like:
I haven't tested this and you will still need to link up to the copying code, but I Hope this helpsCode:Sub FindAllRows() Dim iLoop As Integer Dim rNa As Range Dim rUsedRange As Range Dim i As Integer iLoop = WorksheetFunction.CountIf(ActiveSheet.Cells, 10) Set rNa = Range("A1") Set rUsedRange = ActiveSheet.UsedRange For i = 1 To iLoop Set rNa = rUsedRange.Offset(i, 0).Find(What:=10, After:=rNa, _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=True) rNa = Nothing Next i End Sub