locked
Worksheet Change with multiple changes RRS feed

  • Question

  • I have a worksheet (Current Log) that will move a full row of information to another worksheet (Completed Log) depending on status. Code below... I need to have it do a little more. (I.E. if The target vlue =("Long Term") Then with sheets ("Long Term")  I can't seem to make this work any help is appreciated!

     

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Dest As Range
        If Target.Count > 1 Then Exit Sub
        If IsEmpty(Target.Value) Then Exit Sub
        If Target.Column = 3 And _
            Target.Row > 2 And _
            (Target.Value = "Completed") Then
            With Sheets("Completed Log")
                Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
            End With
            Range(Cells(Target.Row, 1), Cells(Target.Row, 14)).Copy Dest
            Target.EntireRow.Delete


    End If


    End Sub

    Thursday, January 12, 2012 3:53 PM

Answers

  • Here's one of several ways -

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Dest As Range
         If Target.Count > 1 Then Exit Sub
         If IsEmpty(Target.Value) Then Exit Sub ' redundant !
         If Target.Column = 3 And Target.Row > 2 Then
                    Select Case Target.Value
                    Case "Completed": Set ws = Worksheets("Completed Log")
                 Case "Long Term": Set ws = Worksheets("Long Term")
                    End Select
                    If Not ws Is Nothing Then
                         Set Dest = ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
                         Range(Cells(Target.Row, 1), Cells(Target.Row, 14)).Copy Dest
                         Target.EntireRow.Delete
                 End If
            End If
    
    End Sub

    You might want to use say LCase$ to remove case sensitivity

    Peter Thornton

    • Proposed as answer by danishani Wednesday, January 18, 2012 7:22 PM
    • Marked as answer by danishani Friday, January 20, 2012 10:41 PM
    Thursday, January 12, 2012 6:44 PM