CODEWITHSUNDEEP
excelvba
user552769
user552769

Reputation: 101

Copy Cell to another column on change

I need to copy the contents of a cell in a particular column to another corresponding column on change so the old value is moved. Only wants to work for a particular column. 

Private sub Worksheet_Change(ByVal Target As Range)

if Target.Range("L:L") then
'set I cell value = to original L cell value
ActiveCell.Offset(0,-3).Value = ActiveCell.Value
End If

End Sub

Upvotes: 0

Views: 114

Answers (2)

Gary's Student
Gary's Student

Reputation: 96781

This will save the previous value in column I:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim v As Variant
    If Target.Count > 1 Then Exit Sub
    If Intersect(Range("L:L"), Target) Is Nothing Then Exit Sub
    With Application
        v = Target.Value
        .EnableEvents = False
        .Undo
        Target.Offset(0, -3).Value = Target.Value
        Target.Value = v
        .EnableEvents = True
    End With
End Sub

EDIT#1:

To update L without triggering the event, use something like:

Sub ScriptThatUpdatesColumn_L()
    Application.EnableEvents = False
        Range("L5").Value = "just me"
    Application.EnableEvents = True
End Sub

Upvotes: 0

Variatus
Variatus

Reputation: 14383

This code should do what you want. Please take note of the comments which explain some limitations I have imposed on the action of this procedure. The rule to follow is to not give it more power than it needs to do the job you want it to do. 

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 027
    Dim Rng As Range

    ' don't react if the changed cell is in row 1 or
    '   if it is more than 1 row below the end of column L
    Set Rng = Range(Cells(2, "L"), Cells(Rows.Count, "L").End(xlUp).Offset(1))

    If Not Application.Intersect(Target, Rng) Is Nothing Then
        With Target
            ' skip if more than 1 cell was changed
            '   meaning, exclude paste actions
            If .Cells.CountLarge = 1 Then
                .Offset(0, -3).Value = .Value
            End If
        End With
    End If
End Sub

Upvotes: 1

Related Questions