excel - Select and extract row of data to another sheet -

i'm working big worksheet containing stocks information, columns organized :

    id     date       time     price       quantity    nbe 

it goes on 500k+ rows, , have 10+ sheets go through. need extract first 2 trade of each trading day, , create new list on new sheet (sheet1). first trade of every day @ "09:00:00".

so far wrote piece of code, in tried copy 2 lines need , paste them sheet1 creating new list. runs without errors, nothing shows up...

sub macro1()  = 2 range("c2").select range(selection, selection.end(xldown)).select  each cell in selection  if day(.range("b" & crow).value) <> day(.range("b" & crow - 1).value)     activecell.entirerow.copy     activeworkbook.sheets("sheet1").rows(i).paste     activecell.offset(1).copy     activeworkbook.sheets("sheet1").rows(i + 1).paste     = + 2 end if next cell  end sub 

shouldn't select , copy paste 2 rows together? or possible create range consisting of 2 rows , 6 columns activecell , copy paste range?

edit 1: it's not working.. updated above, still error 438 here activeworkbook.sheets("sheet1").rows(i).paste

edit 2: i'm def big noob. realized not every first trade made @ 9:00:00 need select row based on wether or not 1 day have passed, , select first two. can use condition instead : if day(range("b" & crow).value) <> day(range("b" & crow - 1).value) ?

this should

make sure on sheet data , run it, , copy onto sheet1 in same workbook starting @ row2

you should make sure sheet1 empty , .clearcontents

sub macro1()      dim lngfirstrow long     dim lnglastrow long     dim crow long     dim shsrc worksheet      dim lngnextdestrow long     dim shdest worksheet      application.screenupdating = false      set shsrc = activeworkbook.activesheet     set shdest = activeworkbook.sheets("sheet1")      shsrc          lngfirstrow = 2         lnglastrow = .cells.find(what:="*", after:=.cells.cells(1), lookat:=xlpart, lookin:=xlformulas, searchorder:=xlbyrows, searchdirection:=xlprevious, matchcase:=false).row         lngnextdestrow = 2          crow = lngfirstrow lnglastrow step 1              if format(.range("c" & crow).value, "hh:mm:ss") = "09:00:00"                  .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      application.screenupdating = true end sub