Search code examples
excelvbacopy-paste

Excel VBA pastes modified value instead original one

My macro below inserts value (Hello) into cell A1 and copies this value using copy method. Then it changes value in A1 (Hi).

At the end I would like to paste (original) value into another cell e.g. B1. I'm very surprised that Excel pastes modified value instead original one (why? copying has been before modifying!).

Notice: My macro starts with CopyAndPasteData().

Sub CopyAndPasteData()
  Range("A1") = "Hello"
  CopyData
  Range("A1") = "Hi"
  PasteData
End Sub

Sub CopyData()
  Range("A1").Copy
End Sub

Sub PasteData()
  Range("B1").PasteSpecial
End Sub
Solution 
  • sub test()
    dim s as String
    Range("A1") = "Hello"
    s = Range("A1").value
    Range("B1").value = s
    PasteData
end sub

    This is the best thing you can do. Create a variable and assign the value of A1 to the particular variable so that you can use the first value anywhere by accessing the variable.