Copy and Paste Special with VBA

Question

I have a VBA script that copies a column (from an Excel spreadsheet) with formulas and pastes only the result (Paste Special).

But I would like to automate this task, example:
Whenever you add any information in column "A", the data in column "B" is automatically updated.

Follow an example of the code:

Public Sub pasteVal()
   Range("A1:A10").Select
   Selection.Copy
   Range("B1:B10").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False
End Sub

Answer 1

Miguel,

Just one example:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:C10")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Display a message when one of the designated cells has been 
        ' changed.
        ' Place your code here.
        MsgBox "Cell " & Target.Address & " has changed."

    End If

End Sub

When a cell is changed in Excel, it fires this sub.

See more in:

How to run a macro when some cells are changed in Excel