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

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

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