vba - excel click object stores value from one cell to another -

i'm trying work on in excel vba can't seem make work fine.

here's how should work: need copy , paste value of "j21" cell 1 sheet another. but, value of j21 keeps on changing every week. thought if create code i'll press object (say "store!") , copies value of "j21" sheet1 "c3" sheet 2. when value of j21 changes, press "store!" again , copy value of j21 , paste on "c4" sheet 2 without changing previous value on "c3" sheet 2.

here's latest attempt:

dim mycell range, myrange range, long  = 3  set myrange = sheets("summary").range("c3")  set myrange = range(myrange, myrange.end(xldown))  sheets("sheet1").select  range("j21").copy  sheets("summary").select  cells(i, 3).select  selection.pastespecial paste:=xlpastevalues, operation:=xlnone,                         skipblanks:=false, transpose:=false  = + 1  

mycell , myrange used previous attempts, go on infinite copy-paste.

selection , copy-paste in vba not needed. if want modify value of cell better directly use value property of range object. seem want establish reference first blank cell below c2 on sheet2. problem code range(myrange, myrange.end(xldown)) selects entire block of cells (above cell want) rather single cell. while possible use .end(xldown) appropriately cell want, tricky right since behaves differently depending on whether or not cell underneath current cell blank. while loop 1 way go. like:

sub store()     dim target range, source range     set source = sheets("sheet1").range("j21")     set target = sheets("sheet2").range("c3")     while not isempty(target.value)         set target = target.offset(1)     loop     target.value = source.value end sub 

chip's suggestion learn basics of vba one. if (like me) learns through books better online tutorials, recommend john walkenbach -- first learned program excel reading edition of "excel vba programming dummies."