Get Data to Populate from one Worksheet to Another, Based on data entered

[marshak]

Hello all! Please help!

The following shows my table in Sheet 1. When I enter "Y" into any cell under Column G (Key Event?) (Sheet1), I need the row to automatically populate in another worksheet called Key Events.

(...and if possible, delete the row from the Key Events worksheet if "Y" is replaced with "N" in Sheet 1) -- if this is not an easy task, then please just focus on the initial task above.

Thank you so much!!

100478__130644324v1_CF Chronology.xlsx
	A	B	C	D	E	F	G
1	Date	Event	Source	Designation	Primary Relevance	Secondary Relevance	Key Event?
2	1995	Event 1	Source 1		Caputo
3	1994-1997	Event 2	Source 2		Caputo
4	1997-2002	Event 3	Source 3		Caputo
5	June 1997-Dec. 2002	Event 4	Source 4		Caputo
6	2000	Event 5	Source 5		USRC Background	Caputo
7	2002	Event 6	Source 6		Caputo
8	8/27/2003	Event 7	Source 7		Dorsey Connections	Caputo
9	1/22/2007	Event 8	Source 8		Dorsey Connections
10	5/30/2007	Event 9	Source 9		Dorsey Connections
11	6/22/2007	Event 10	Source 10		Dorsey Connections
12	7/17/2008	Event 11	Source 11		Dorsey Connections
Master Chron (2)

Cells with Data Validation
Cell	Allow	Criteria
F6:F12	List	=Relevance_List
D6:D12	List	=Designation_List

 

[vcoolio]

Hello Marshak,

The following event code placed into the Sheet1 module should do the task for you:-

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Columns(7)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Value = vbNullString Then Exit Sub
    
    Application.ScreenUpdating = True
    
    If Target.Value = "Y" Then
            Target.EntireRow.Copy Sheets("Key Events").Range("A" & Rows.Count).End(3)(2)
            ElseIf Target.Value = "N" Then
            Target.EntireRow.Delete
    End If
    
    Sheets("Key Events").Columns.AutoFit
    Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[marshak]

Hello Marshak,

The following event code placed into the Sheet1 module should do the task for you:-

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Columns(7)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Value = vbNullString Then Exit Sub
   
    Application.ScreenUpdating = True
   
    If Target.Value = "Y" Then
            Target.EntireRow.Copy Sheets("Key Events").Range("A" & Rows.Count).End(3)(2)
            ElseIf Target.Value = "N" Then
            Target.EntireRow.Delete
    End If
   
    Sheets("Key Events").Columns.AutoFit
    Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

THANK YOU SO VERY MUCH!!

 

[vcoolio]

You're welcome Marshak. I'm glad to have been able to assist.

Cheerio,
vcoolio.