One play which automates it using non-array formulas ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3edgh
AutoCopy Lines by Currency into its Own Sht.xls

In sheet: WS1 (the "master")
Assume source data as posted is in cols A to E, data in row2 down,
with the key col = col E (Currency)

List the currencies in K1 across: USD, EUR, JPY, etc (can be in any order)
Put in J2: =E2
Put in J3: =IF(E3="",J2,E3)
Copy J3 down to the last row of source data

Put in K2: =IF($J2=K$1,ROW(),"")
Copy K2 across as far as required, fill down

Click Insert Name Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.

Then, in a new sheet named after one of the currencies, eg: USD
With the same col headers pasted into A1:D1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1! $K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(WS1!A:A,MA TCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV $1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN, WS1!$K$1:$IV$1,0)),0)))
Copy A2 across to D2, fill down to say, D100, to cover the max expected
extent for any currency. Cols A to D will return only the lines for the
currency: USD from "WS1", with all lines neatly bunched at the top

Now, just make a copy of the sheet: USD, rename it as the next currency:
EUR, and you'd get the results for that currency. Repeat the copy rename
sheet process to get the rest of the currency sheets (a one-time job) as
required. Adapt to suit ..

P/s: You should have used a more meaningful subject line, and never use all
CAPS - it's considered impolite
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
...
I need a Function to copy a series of Rows (each four Rows Together)
dependent on a criteria on the same sheet and paste it in another
Sheet.

EXAMPLE:

111 222 333 444 USD
#
111 222 333 555
#
111 333 444 555 EUR
#
111 222 333 444
#
111 333 444 555 EUR
#
111 222 333 777
#
111 333 444 112 JPY
#
111 222 333 000
#

I would like copy each 4 rows (each 4 starting with the row containing
the currency) and paste them on a different sheet dependent on the
currency. So if I had 2 groups(4 rows each) in EUR(like the example
above) i would like to past them in the "EUR" Sheet. And the ones
containg the USD to the "USD" and the same for JPY (and other
currencies as well).

I hope I was able to explain my intention..and thank you in advance.