Hi Anthony -

This code operates on the activesheet and is ready to run assuming the
specifications in your original post have not changed ("RBK", Col F, etc).
It permits multiple instances of RBK in the search column. Modify if
necessary and let me know if you have any problems.

Option Base 1
Sub Anthony()

Dim ws As Worksheet
Dim lz, searchRng, rbkCell, img() As Range

Set ws = ActiveSheet
Set lz = ActiveCell
Set searchRng = ws.Columns("F")
sSearchCriterion = "RBK"

While Not searchRng.Find(sSearchCriterion, LookIn:=xlValues,
lookat:=xlPart) Is Nothing

Set rbkCell = searchRng.Find(sSearchCriterion, LookIn:=xlValues,
lookat:=xlPart)
If rbkCell Is Nothing Then MsgBox sSearch & " string not found.":
Exit Sub

Set block = Range(rbkCell, rbkCell.End(xlDown))

If Not block.Find(Null) Is Nothing Then
MsgBox "Anomaly found; empty cell follows RBK... Examine or fix
data and rerun this procedure."
block.Find(Null).Select
Exit Sub
End If

icount = icount + 1
Set block = block.Offset(1, 0).Resize(block.Rows.Count - 1, 1)
ReDim Preserve img(1 To icount)
Set img(icount) = Range(Cells(block.Row, block.Column - 3), _
Cells(block.Row + block.Rows.Count - 1,
block.Column + 4))

Set searchRng = Range(Cells(block.Row, searchRng.Column),
Cells(65536, searchRng.Column))

Wend

For i = 1 To icount
img(i).Copy
Destination:=ActiveSheet.Range("Z65536").End(xlUp) .Offset(1, 0)
Next i

lz.Select
End Sub

--
Jay


"Anthony" wrote:

Hi,

I have a worksheet of data.
I would like some code that will search down column F for 'RBK' when found
copy cells C:J of the next row, paste this into next avaiable row in column
Z. Then copy next rows cells C:J and paste again into next available row in
row Z - keep doing this until there is a blank cell in column F
eg
the data below is a sample, the RBK is found in cell F4, so as a result the
following 3 lines of data should be copy/pasted to next available row in
column Z.
the 4th,5th and 6th are NOT copies as there is a gap (or empty row) between
the sets of data.
.............................RBK
619994 Johnson 04J08G 4DK A5 4:45 13:15
777264 Kaleem 04J08G 1FJ A5 4:45 13:15
704825 Afshan 04J08G 4DK A5 4:45 13:15

701636 Young 04J08G 4FJ A5 4:45 13:15
811513 Carver 06A08G 4DK A5 6:00 14:30
681142 Crowther 06A08G SPA A5 6:00 14:30


..Hope this makes sense and thanks in advance for your help