Copy data from one worksheet to another under condition

[danijela_s3]

Hi,
I need to copy data from one worksheet to another under condition.
For example, in the master sheet I have employee names with certian skill sets. These skill sets need to be divided on the separate worksheet that highlights only employees with that certain skill set.


Employee Name Skill 1 Skill 2 Skill 3
Employee 1 X X
Employee 2 X
Employee 3 X
Employee 4 X X
(note: x should be under different skill sets it does not show here well)

What would be the best formula to use here?

Thanks a lot!

 

[jbeaucaire]

This is the layout you presented. I've added a dropdown box in cell I1 that presents the skills to check.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	NAME	Skill1	Skill2	Skill3				Names	Skill2
2	Emp 1	X	X					Emp 1
3	Emp 2	X		X				Emp 3
4	Emp 3	X	X					Emp 4
5	Emp 4	X	X	X
Sheet1

Now, if you right-click on your sheettab and select VIEW CODE, this worksheet_change macro will watch the choice you make in cell I1 and give you a list of names in column H.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I1")) Is Nothing Then
Dim lastrow As Long

lastrow = ActiveSheet.UsedRange.Rows.Count
Range("H2:H1000").ClearContents
    
    Select Case Range("I1").Value
        Case "Skill1"
            For Each ce In Range("B2:B" & lastrow)
                If ce.Value = "X" Then
                Range("H" & Cells(Rows.Count, 1).Row).End(xlUp).Offset(1, 0).Value = Range("A" & ce.Row).Value
                End If
            Next ce
        Case "Skill2"
            For Each ce In Range("C2:C" & lastrow)
                If ce.Value = "X" Then
                Range("H" & Cells(Rows.Count, 1).Row).End(xlUp).Offset(1, 0).Value = Range("A" & ce.Row).Value
                End If
            Next ce
        Case "Skill3"
            For Each ce In Range("D2:D" & lastrow)
                If ce.Value = "X" Then
                Range("H" & Cells(Rows.Count, 1).Row).End(xlUp).Offset(1, 0).Value = Range("A" & ce.Row).Value
                End If
            Next ce
        Case Else
        Exit Sub
    End Select
End If
End Sub