Copy from sheet 1 to sheet 2 based on criteria - VBA

  • I'm trying to refine my VBA code to copy from sheet 1 two sheet 2 based on a criteria.


    sheet 1 contains a list of jobs. Column B of sheet 1 contain the job number. For all job numbers in Column B which begin with a "EA" or "200" or "13" I want to copy that row to sheet 2.
    eg [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 79"]Date[/TD]
    [TD="width: 98"]Job Number[/TD]
    [TD="width: 389"]Description[/TD]
    [TD="width: 72"]Engineer[/TD]
    [TD="width: 79"]Target[/TD]

    [/tr]


    [tr]


    [td]

    16/03/2018

    [/td]


    [td]

    EA088671/2/1

    [/td]


    [td]

    BLOCK & BLEED

    [/td]


    [td]

    A smith

    [/td]


    [td]

    28/03/2018

    [/td]


    [/tr]


    [/TABLE]

    [VBA]Sub CopyRows()
    Dim bottomB As Integer
    Dim x As Integer
    bottomB = Sheets("Job List").Range("B" & Rows.Count).End(xlUp).Row: x = 1

    Dim c As Range
    For Each c In Sheets("Job List").Range("B2600:B" & bottomB)
    If c.Value = "EA" Then
    c.EntireRow.Copy Worksheets("Spares").Range("A" & x)
    x = x + 1
    End If
    Next c
    End Sub [/VBA]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!