Macro to populate certain cells of information per row from one worksheet to another worksheet in the same workbook based on a certain criteria.

[FOMOF]

I am trying to create a macro that populates a new worksheet based off of a master worksheet. The data being populated is non-contiguous in the same row and only populates if a particular cell in that row is populated with "Y" as data. (Example below).

Worksheet 1 (Master)
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]ID[/TD]
[TD]PRIORITY[/TD]
[TD]TEAM NAME[/TD]
[TD]DESCRIPTION[/TD]
[TD]TEAM LOCATION[/TD]
[TD]START DATE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]TEAM A[/TD]
[TD]PRIMARY[/TD]
[TD]LONDON[/TD]
[TD]9.10.15[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Y[/TD]
[TD]TEAM B[/TD]
[TD]SECONDARY[/TD]
[TD]SINGAPORE[/TD]
[TD]10.11.15[/TD]
[/TR]
</tbody>[/TABLE]

If column B "Priority" is populated with a "Y" then populated worksheet 2 with the following data from worksheet 1

Worksheet 2 (New) After running Macro

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Team Name[/TD]
[TD]TEAM LOCATION[/TD]
[TD]START DATE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]TEAM B[/TD]
[TD]SINGAPORE[/TD]
[TD]10.11.15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

*Note that Team one does not populate because column B is not populated with a "Y" and the description column from worksheet 1 does not get get pulled in to worksheet 2. Does anyone have a recommendation for this combination of requirements?

Thank you

FOMOF

 

[Learning To Excel]

I would do something like this:

Sub Transplant()
Dim i As Integer
Dim k As Integer
k = 2
    Sheets("New").Cells.Clear
    Sheets("Master").Rows(1).Copy Destination:=Sheets("New").Rows(1)

    For i = 1 To Sheets("Master").Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
        If Sheets("Master").Cells(i + 1, 2).Value = "Y" Then
            Sheets("Master").Rows(i + 1).Copy Destination:=Sheets("New").Rows(k)
            k = k + 1
        End If
    Next i
Application.CutCopyMode = False
Range("A1").Select
End Sub

 

[FOMOF]

Thank you, Learning To Excel, this is really close to perfect. What code would I need amend to have the data on the new worksheet begin populating on row 12 column 2 instead of A2? Also, I need to be able to select specific cells in the rows versus bringing the whole row in to the new spreadsheet ex. instead of all the data in row B12:H12, I need to be able to pick B12, E12, G12:K12, and U12:V12. It will be the same column letters for every row, just not ALL the column letters.

Thanks!

 

[Learning To Excel]

You could use something like this:

Sub Transplant()
Dim i As Integer
Dim k As Integer
k = 12

    For i = 2 To Sheets("Master").Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
        If Sheets("Master").Cells(i, 2).Value = "Y" Then
            Sheets("Master").Range("B" & i).Copy Destination:=Sheets("New").Range("B" & k)
            Sheets("Master").Range("E" & i).Copy Destination:=Sheets("New").Range("E" & k)
            Sheets("Master").Range("G" & i & ":K" & i).Copy Destination:=Sheets("New").Range("G" & k & ":K" & k)
            Sheets("Master").Range("U" & i & ":V" & i).Copy Destination:=Sheets("New").Range("U" & k & ":V" & k)
            k = k + 1
        End If
    Next i
Range("A1").Select
End Sub