excel - Find particular cells to copy to main workbook -

i want find particular cell in opened worksheet , copy data 3 cells right main workbook (not 1 has been opened).

the macro below loops through files in txt doc , opens , closes them. in each file cell called ada , want copy cells 3 right, ada not in same place need search it. e.g find cell ada , in e6 in workbook, need copy e6 along h6,i6 , j6 original main workbook running macro from.

sub gatherdata() dim objfso object dim objwb workbook dim strfn string dim objtf object  set objfso = createobject("scripting.filesystemobject") set objtf = objfso.opentextfile("u:\time series project\doclist.txt") on error resume next while not objtf.atendofstream     strfn = objtf.readline()     set wb = workbooks.open(strfn)     if wb nothing         debug.print strfn     else             wb.close false         set wb = nothing     end if loop on error goto 0 end sub 

i thinking of adding like

dim c range dim newcell dim tmp dim wrkbk workbook dim sht worksheet  set c = .find(findvalues(i), lookin:=xlformulas) if not c nothing     newcell = c.offset(0, 4).value             tmp.offset(0, 2).value = tmp.value         tmp.offset(0, 3).value = newcell end if 

but can't concept fit first code.

you can use

dim copyrange range dim originalcell, startingcell, endingcell string      originalcell = sht.range("ada").address     startingcell = sht.range("ada").offset(0,3).address     endingcell = sht.range("ada").offset(0,5).address      copyrange = originalcell & "," & startingcell & ":" & endingcell 

then copy range , paste like.