Per, thank you very much. It works, however when all blocks of data are
copied I am getting a run time error 1004, being a newbie again I have to ask
any suggestions
--
thanks as always for the help
jer

"Per Jessen" wrote:

Hi

Assuming "End of Report" is in the first Cell after the data, try this:

Set aCell = Sheets("Sheet1").Range("A1")
Set trCell = Sheets("Region Y").Range("A1")
Do Until aCell.Offset(-2, 0).Value = "End of Report"
If Left(aCell, 7) = "Region:" And _
Left(aCell.Offset(2, 0), 2) = "b2" Or _
aCell.Offset(2, 0) = "b3" Then
Range(aCell, aCell.End(xlDown).Offset(0, 4)).Copy trCell
Set trCell = trCell.End(xlDown).Offset(2, 0)
End If
Set aCell = aCell.End(xlDown).Offset(3, 0)
Loop

Regards,
Per

"jer" skrev i meddelelsen
...
Some assistance please - I am working with a spreadsheet that has been
imported from some other system, with skipped rows. I want to move
similar
blocks of data from this spread sheet to another for example all where
abbrev
begins with b2 to spreadsheet named "bregion" and for beginning tm to
sheet
named tme
Region: 123456
Abbrev Name Invoice Date Amount
b2btr jon publ 01/15/2009 20.00
b2btr jon publ 03/02/2009 552.00
b2btr jon publ 03/02/2009 321.35
blank rows
blank rows
Region: 12356
Abbrev Name Invoice Date Amnt
tbmna jane done 12/22/2008 123.55
tbmna jane done 12/22/2008 99.89
blank rows
blank rows
Region: 3457
Abbrev Name Invoice Date Amnt
b2brr peter jones 03/02/2009 23.50
b2brr peter jones 03/03/2009 23.00
b2brr peter jones 03/03/2009 2.90

I have attempted the following but it copies the first block not not
subsequent blocks; acell is referene on original sheet trcell is cell
reference on sheets(regiony) where data is being copied to

do until acell = "End of Report"
If Left(acell, 7) = "Region:" And _
Left(acell.Offset(2, 0), 2) = "b2" Or _
acell.Offset(2, 0) = "b3" Then
Range(acell, acell.End(xlDown).Offset(0, 4)).Select
Selection.Copy
Sheets("regionY").Select
ActiveSheet.Paste
Application.CutCopyMode = False
trcell.End(xlDown).Offset(2, 0).Select
End If
Set acell = acell.Offset(1, 0)
Loop
--
thanks as always for the help
jer