Copying selected rows into new worksheet
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 |
Copying selected rows into new worksheet
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 |
Copying selected rows into new worksheet
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 |
All times are GMT +1. The time now is 01:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com