Select and extract row of data to another sheet

Question

I'm working with big worksheet containing stocks information, with columns organized like this :

    ID     DATE       TIME     PRICE       QUANTITY    NBE

It goes on for 500k+ rows, and I have 10+ sheets to go through. I need to extract only the first two trade of each trading day, and create a new list on a new sheet (Sheet1). The first trade of every day is always at "09:00:00".

So far I wrote this piece of code, in which I tried to copy the two lines I need and then paste them into Sheet1 thus creating the new list. It runs without errors, but nothing shows up...

Sub Macro1()

i = 2
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select

For Each Cell In Selection
 If Day(.Range("B" & cRow).Value) <> Day(.Range("B" & cRow - 1).Value) Then
    ActiveCell.EntireRow.Copy
    ActiveWorkbook.Sheets("Sheet1").Rows(i).Paste
    ActiveCell.Offset(1).Copy
    ActiveWorkbook.Sheets("Sheet1").Rows(i + 1).Paste
    i = i + 2
End If
Next Cell

End Sub

Shouldn't i select and the copy paste the two rows together? Or is it possible to create a range consisting of 2 rows and 6 columns from the activecell and then copy paste that range?

EDIT 1: It's not working.. I updated it like above, but I still get an error 438 here ActiveWorkbook.Sheets("Sheet1").Rows(i).Paste

EDIT 2: I'm def a big noob. Just realized not every first trade was made at 9:00:00 so i need to select the row based on wether or not one day have passed, and select the first two. Can I use this condition instead : If Day(Range("B" & cRow).Value) <> Day(Range("B" & cRow - 1).Value) Then ?

I suspect using Cell instead of ActiveCell inside your loop would be of some help. That said, while I am not entirely sure whether or not Cell is a reserved term in VBA, I think I would have named my variables something less likely to already have a use. — eirikdaude
Set the range to search using something like Dim range_to_search As Range: Set range_to_search = Range(ActiveWorksheet.Range("C2"), ActiveWorksheet.Range("C2").End(xlDown) instead. Honestly I'd prefer it if you referenced the worksheet to search explicitly (like you do with Sheet 1 further down the macro) instead of using ActiveWorksheet too, but that's not quite as egregious. — eirikdaude

Steven Martin Steven Martin · Accepted Answer · 2015-03-20T21:44:27

This should do it quickly

make sure your on the sheet with data and run it, and it will copy it onto sheet1 in the same workbook starting at row2

you should make sure sheet1 is empty also , with .clearContents

Sub Macro1()

    Dim lngFirstRow As Long
    Dim lngLastRow As Long
    Dim cRow As Long
    Dim shSrc As Worksheet

    Dim lngNextDestRow As Long
    Dim shDest As Worksheet

    Application.ScreenUpdating = False

    Set shSrc = ActiveWorkbook.ActiveSheet
    Set shDest = ActiveWorkbook.Sheets("Sheet1")

    With shSrc

        lngFirstRow = 2
        lngLastRow = .Cells.Find(What:="*", After:=.Cells.Cells(1), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
        lngNextDestRow = 2

        For cRow = lngFirstRow To lngLastRow Step 1

            If Format(.Range("C" & cRow).value, "hh:mm:ss") = "09:00:00" Then

                .Rows(cRow).EntireRow.Copy Destination:=shDest.Range("A" & lngNextDestRow )

                   .Rows(cRow+1).EntireRow.Copy Destination:=shDest.Range("A" & lngNextDestRow+1 )

                lngNextDestRow = lngNextDestRow + 2

            End If
        Next cRow

    End With

    Application.ScreenUpdating = True
End Sub

Select and extract row of data to another sheet

3 Answers