Copying selected rows into new worksheet

P

PVANS

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
 
J

Jacob Skaria

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top