# Formula - copy cell if another cell contains YES

#### BravoBravoAu

##### Board Regular
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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi
This code in sheet2 code
VBA 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``````

Hi
This code in sheet2 code
VBA 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?

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".

Hi
VBA Code:
``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:
VBA Code:
``Cells(1, 1).Resize(.Count, [B]5[/B]) = Application.Index(.items, 0, 0)``
BTW You can delete this line
VBA Code:
``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

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?

Just change to

VBA Code:
`` Cells(1, 3).Resize(.Count, 5) = Application.Index(.items, 0, 0)``

Replies
1
Views
183
Replies
13
Views
3K
Replies
0
Views
273
Replies
5
Views
116
Replies
10
Views
271

1,203,483
Messages
6,055,675
Members
444,807
Latest member
RustyExcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back