excel click object stores value from one cell to another

Question

I'm trying to work on something in excel VBA but I can't seem to make it work fine.

Here's how it should work: I need to copy and paste value of "J21" cell from one sheet to another. BUT, value of J21 keeps on changing every week. So I thought what if I create a code where I'll just press an object (say "STORE!") and it copies the value of "J21" from Sheet1 to "C3" Sheet 2. Then when the value of J21 changes, I just press "STORE!" again and it will copy the value of J21 and paste it on "C4" Sheet 2 without changing the previous value on "C3" Sheet 2.

Here's my latest attempt:

Dim myCell As Range, myRange As Range, i As Long 
i = 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 
i = i + 1 

myCell and myRange were used for my previous attempts, but it always go on an infinite copy-paste.

Answer 1

Selection and copy-paste in VBA is usually not needed. If you want to modify the value of a cell it is better to just directly use the value property of a range object. You seem to want to establish a reference to the first blank cell below C2 on Sheet2. The problem with your code is that Range(myRange, myRange.End(xlDown)) selects an entire block of cells (above the cell you want) rather than a single cell. While it would be possible to use .End(xlDown) appropriately to get to the cell you want, it is somewhat tricky to get right since it behaves differently depending on whether or not the cell underneath the current cell is blank. A while loop is one way to go. Something like:

Sub store()
    Dim target As Range, source As Range
    Set source = Sheets("Sheet1").Range("J21")
    Set target = Sheets("Sheet2").Range("C3")
    Do While Not IsEmpty(target.Value)
        Set target = target.Offset(1)
    Loop
    target.Value = source.Value
End Sub

Chip's suggestion that you learn the basics of VBA is a good one. If you are someone (like me) who learns through books better than with online tutorials, I would recommend almost anything by John Walkenbach -- I first learned to program Excel by reading an early edition of his "Excel VBA Programming for Dummies."