ii..i have a problem to copy the value..for the example...in "sheet1" in column "A1" have value=2 and at the same time the column in "A1" have a formula "=Sheet2!A1"....how i want to copy the value from column "A1" to column "A5" in the same sheet"Sheet1"... i can't use this coding...

With Worksheets("Sheet1")
    .Range("A1").Copy
    .Range("A5").PasteSpecial _
        Operation:=xlPasteSpecialOperationAdd
End With

this coding doesn't work in this situation...it is because the value in A1 have a formula....can u help me solve this problem..tq very much

replace your last line of code with the following.

.Range("A5").PasteSpecial Paste:=xlValues, Operation:=xlAdd

cheers

Matt