thanks Jacob,

it worked like a charm :)

Have a good day

Regards,

"Jacob Skaria" wrote:

Try the below macro..which will copy the row just before blank row to Sheet2.

Sub MyMacro()
Dim lngRow As Long, lngNRow As Long
Dim ws2 As Worksheet
Set ws2 = Sheets("Sheet2")
lngNRow = 1

For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row + 1
If Trim(Range("A" & lngRow)) = "" Then
Rows(lngRow - 1).Copy ws2.Rows(lngNRow)
lngNRow = lngNRow + 1
End If
Next
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


"PVANS" wrote:

Good afternoon everyone, hope someone can help me with this

I have a worksheet that has blocks of transactions, with a summary
transaction for each one at the bottom of the block, and blank rows
seperating the different blocks of trades.

The blocks are seperated by date and product
-----------------------------------------------------------------------------------------------
EG:
Date Quantity BUY/SELL Price Gross Sale Currency Account Item
20090727 16997 S 5 84985 GBP DEFAULT Coke
20090727 48606 S 5 243030 GBP DEFAULT Coke
20090727 10000 S 5 50000 GBP DEFAULT Coke
20090727 58316 S 4 233264 GBP DEFAULT Coke
20090727 133919 S 19 233264 GBP DEFAULT Coke

20090727 9238 S 5 46190 GBP DEFAULT Pepsi
20090727 4146 S 6 24876 GBP DEFAULT Pepsi
20090727 13384 S 11 24876 GBP DEFAULT Pepsi

etc.
------------------------------------------------------------------------------------------------
I am trying to write a macro that finds the final row of each transaction
(which is a summary of the rows above it) and copies it to a new worksheet.

Will provide further explanation if needed

Really appreciate any advice you can provide

Regards,
PVANS