Formula - copy cell if another cell contains YES

[BravoBravoAu]

Dear Excel geniuses, I hope this is another easy solution.

I have two worksheets:
1. Worksheet 1 contains all details about employees including personal information. Column L is whether the employee is 'active' with a YES/NO list.
2. Worksheet 2 contains a phone list that is widely available and can't include personal information. For active employees (eg those who have YES in Worksheet1, column L), I would like SOME details copied into Worksheet 2.

When employees are changed to inactive (eg those who have NO in Worksheet1, column L), I'm looking for them to 'disappear' from Worksheet2 and the results adjust so there are no row spaces. Worksheet1 wont have information removed.

I hope this helps.

Worksheet2

	A	B	C	D
1	Employee Name	Employee Number	Phone Number	Desk
2	(Copied data from Worksheet1, column A)	(Copied data from Worksheet1, column B)	(Copied data from Worksheet1, column G)	(Copied data from Worksheet1, column E)
3

 

[mohadin]

Hi
This code in sheet2 code

Private Sub Worksheet_Activate()
    a = Sheets("sheet1").Cells(1, 1).Resize(Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row, 12)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If UCase(a(i, 12)) = "YES" Then
                If Not .exists(a(i, 1)) Then: .Add a(i, 1), Array(a(i, 1), a(i, 2), a(i, 7), a(i, 5))
            End If
        Next
        Me.[A:D].ClearContents
        Me.Cells(1, 1).Resize(.Count, 4).Select
        Cells(1, 1).Resize(.Count, 4) = Application.Index(.items, 0, 0)
    End With
End Sub

 

[BravoBravoAu]

Hi
This code in sheet2 code

Private Sub Worksheet_Activate()
    a = Sheets("sheet1").Cells(1, 1).Resize(Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row, 12)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If UCase(a(i, 12)) = "YES" Then
                If Not .exists(a(i, 1)) Then: .Add a(i, 1), Array(a(i, 1), a(i, 2), a(i, 7), a(i, 5))
            End If
        Next
        Me.[A:D].ClearContents
        Me.Cells(1, 1).Resize(.Count, 4).Select
        Cells(1, 1).Resize(.Count, 4) = Application.Index(.items, 0, 0)
    End With
End Sub

Thanks @mohadin, appreciate your assistance with this solution.

If I needed to add the fields copied into Worksheet2 to include column I, I assume, I would modify the relevant line in the code to:
If Not .exists(a(i, 1)) Then: .Add a(i, 1), Array(a(i, 1), a(i, 2), a(i, 7), a(i, 5), a(i, 9))

Is this the only change needed to extend the code?

So that I am clear, this solution will need to pick up 15 or 20 different active employees from Worksheet1 at any given time - not just one. Can you confirm this will work?

 

[BravoBravoAu]

Thanks @mohadin, appreciate your assistance with this solution.

If I needed to add the fields copied into Worksheet2 to include column I, I assume, I would modify the relevant line in the code to:
If Not .exists(a(i, 1)) Then: .Add a(i, 1), Array(a(i, 1), a(i, 2), a(i, 7), a(i, 5), a(i, 9))

Is this the only change needed to extend the code?

So that I am clear, this solution will need to pick up 15 or 20 different active employees from Worksheet1 at any given time - not just one. Can you confirm this will work?

When I tried the code, VBA editor reports an "invalid use of Me".

 

[mohadin]

Hi

If Not .exists(a(i, 1)) Then: .Add a(i, 1), Array(a(i, 1), a(i, 2), a(i, 7), a(i, 5), [B]a(i, 9)[/B])

This correct but you will need as well to:

Cells(1, 1).Resize(.Count, [B]5[/B]) = Application.Index(.items, 0, 0)

BTW You can delete this line

Me.Cells(1, 1).Resize(.Count, 4).Select

no need

this solution will need to pick up 15 or 20 different active employees from Worksheet1 at any given time

Confirmed when ever you activate sheet2

"invalid use of Me".

As long as you put the code in sheet2 code should work ok

 

[BravoBravoAu]

Thanks @mohadin - you've smashed it.

My only issue is the information in Worksheet2 'starts' at A1, whereas I have header information in Row A and B. What do I need to adjust to start at C?

 

[mohadin]

Just change to

 Cells(1, 3).Resize(.Count, 5) = Application.Index(.items, 0, 0)