excel - Need the Now Function set in another cell when copying data from Macro -

i have macro takes data clipboard , paste specific cell transposing information.

sub updatedata()          '~~> change relevant sheet     set ws = thisworkbook.sheets("sheet2")        ws         '~~> using copying notepad~~~~         .activate         .range("h1").select         .pastespecial format:="text", link:=false, displayasicon:=false         '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ worksheets("sheet2").range("h1" & ",h3").copy sheets("sheet2").range("c" & rows.count).end(xlup).offset(1).pastespecial transpose:=true  '~~clear data content~~~~~~~~~~~~~~~~~~~~~~~~~ range("h1:h10").clearcontents  end  end sub 

i need macro update cell b next line updated (c) formula.

i have other macro updates row b whenever row c updated, they're not working together.

sub worksheet_change(byval target range) application.moveafterreturn = true if target.count = 1   if not intersect(target, range("c1:c1000")) nothing     cells(target.row, "b") =   end if end if end sub 

any ideas on how should it?

the target cell or cells have changed , triggered worksheet_change event macro. in case, multiple cells have deal each individually.

sub worksheet_change(byval target range)     application.moveafterreturn = true     if not intersect(target, range("c1:c1000")) nothing         on error goto bm_safe_exit         application.enableevents = false         dim c range         each c in intersect(target, range("c1:c1000"))             cells(c.row, "b") =         next c       end if bm_safe_exit:     application.enableevents = true end sub 

turn off event handling while changing data (adding timerstamp) worksheet or trigger worksheet_change run on top of itself.

after determining 1 or more cells in c1:c1000 range has been altered, for each ... next statement cycles through each cell , deposits timestamp on row's column b.