modify vba code to copy only values not formulas

[zoism]

i have the following vba code to copy values from the column A to the next column,but it copy the formulas,i want only the values to be copied, so that when i clear the cell in A not to be cleared in B.

<code>

</code><code>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range, intr As Range, r As Range
    Set A = Range("A:A")
    Set intr = Intersect(A, Target)
    If intr Is Nothing Then Exit Sub

    Application.EnableEvents = False
        For Each r In intr
            If r.Value <> "" Then
                r.Copy r.Offset(0, 1)
            End If
        Next r
    Application.EnableEvents = True
End Sub

</code>
<code></code>

 

[Fluff]

Change this line

<code>r.Copy r.Offset(0, 1)</code>

to

r.Offset(0, 1).Value = r.Value

 

[jim may]

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">After Stepping through this code line >>>: Set intr = Intersect(A, Target) What is the Address of your Range "intr"? Also, on your change event how many cells end up being the target of your change?</code>