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)

ID	PRIORITY	TEAM NAME	DESCRIPTION	TEAM LOCATION	START DATE
1		TEAM A	PRIMARY	LONDON	9.10.15
2	Y	TEAM B	SECONDARY	SINGAPORE	10.11.15

<tbody>
</tbody>

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

ID	Team Name	TEAM LOCATION	START DATE
2	TEAM B	SINGAPORE	10.11.15

<tbody>
</tbody>

*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