Anthony-

Use this version instead of the one I included earlier. This version has a
slight modification that handles a non-existent "RBK" properly. My apologies
for the double post.
--
Jay
-------
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"
icount = 0

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

Set rbkCell = searchRng.Find(sSearchCriterion, LookIn:=xlValues,
lookat:=xlPart)
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

If icount < 1 Then MsgBox sSearchCriterion & " string not found."
lz.Select
End Sub
----------------------------------------------------------------------------------------------



"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