VBA Copy paste columns in different sheet RRS feed

  • Question

  • I have two sheets – sheet 1, sheet 2. I need to copy col M from sheet 1 and paste it into col D of sheet 2 only if sheet 1 col A has the string “COMPATIBLE” and col B has the string “Pass”. Example: Sheet 1:

    A                                               B                                            M

    COMPATIBLE                      Fail                                          1

    NON COMPATIBLE            PASS                                        2

    COMPATIBLE                     PASS                                            3

    COMPATIBLE                    PASS                                        4


    Now in sheet 2 “D” col the value of M should be pasted: i.e:




    Wednesday, January 11, 2017 5:13 PM


  • Sub Test()
        Dim r As Long
        With Worksheets("Sheet 1")
            For r = 1 To .Cells(.Rows.Count, "M").End(xlUp).Row
                If .Cells(r, "A").Value = "COMPATIBLE" And .Cells(r, "B").Value = "PASS" Then
                    Worksheets("Sheet 2").Cells(Rows.Count, "D").End(xlUp)(2).Value = .Cells(r, "M").Value
                End If
            Next r
        End With
    End Sub

    Wednesday, January 11, 2017 7:42 PM