none
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:

    D

    3

    4

    Wednesday, January 11, 2017 5:13 PM

Answers

  • 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