Copy cell if another cell contains YES

[BravoBravoAu]

G'day all. I posted on here a month or two back about this issue and couldn't get the solution to work as intended, so I'm dusting off and trying again. I researched 'copy row to new workbook' but the trick is I don't want all information from Sheet 1 on Sheet 2.

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

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

For example:

Worksheet 2

Employee Name	Employee Number	Position Title	Phone number	Desk Location	Email address
(Copied data from Worksheet1, column A)	(Copied data from Worksheet1, column B)	(Copied data from Worksheet1, column D)	(Copied data from Worksheet1, column G)	(Copied data from Worksheet1, column E)	(Copied data from Worksheet1, column H)

 

[BravoBravoAu]

Hi, After pasting the code on Sheet1, change the Value in Column L to No.

Corresponding record will be removed from Sheet2.

Meanwhile, after pasting the code what's your expectation ?

Apologies @Saurabhj - you did mention that. Got it, that performs as required. Thank you so much for your help!

 

[JW00]

Hi, I think the issue is with how you are pasting the sheet change, so I will attempt to explain below, I have also put a working version on my Google Drive for you to download, it now removes records from sheet 3 too, because sequentially they will be on the same row as sheet 2, so I was mistaken in the assumption that the Employee Number was needed for this, my apologies for that.

Sheet 1 Code

Right click Sheet 1 in the vba window & select view code.



In the left dropdown at the top select worksheet



In the right dropdown at the top select change



Underneath Private Sub Worksheet_Change(ByVal Target As Range) but before End Sub paste

With Sheets("Sheet1")
If Not Intersect(Target, Range("L:L")) Is Nothing Then
If Target = "No" Then
Call Del_Inactive
ElseIf Target = "Yes" Then
Call TransferData
End If
End If
End With



I hope that helps.

 

[BravoBravoAu]

Hi, After pasting the code on Sheet1, change the Value in Column L to No.

Corresponding record will be removed from Sheet2.

Meanwhile, after pasting the code what's your expectation ?

Two other minor follow-up questions please @Saurabhj:
1. If I needed to change which column refers to active (YES/NO) from Column L to Column M, do I just need to update the range - 'If Not Intersect(Target, Range("L:L")) Is Nothing Then' or does the next line of code also need updating as well? I'm not sure what the enpNo line does...

2. If I require additional rows from Sheet1 to Sheet2, do I just need to update the range to add the relevant column - '.Range("J" & nextRow) = Sheets("Sheet1").Range("J" & Target.Row)'?

 

[Saurabhj]

1. Yes, change the range.

If Not Intersect(Target, Range("M:M")) Is Nothing Then

2. If you added new rows in Sheet 1, if you add/ change column value to YES, row will be added to Sheet2 automatically. For this you don't need to change the code.

3. If you are adding extra column in Sheet 1 and corresponding value in Sheet 2 then you need to change the code.

[I]'.Range("J" & nextRow) = Sheets("Sheet1").Range("J" & Target.Row)[/I]'

Hope it clarifies.