Excel

[DrDrPath]

Hello,

New here. I've been looking at other similar solutions to what I'm trying to do but a little bit stumped.

When my entries in the N column = yes, I would like for the entry to be copied, deleted and pasted/ cut and pasted from the New Visitors Sheet and moved to the next available row in Completed Visits.

Thanking you all kindly in advanced.

Visit Logbook.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date Requested	Name (Firstname, Last name)	Email Contact	Designation	Current Institution	Current Department	Dates for visit	Number of Days Total	Confirmed	Notified Administrator for paperwork	Welcome Letter Provided	Timetable Provided	Feedback Completed	Session Complete?
2	02/08/2022	John Smith	john.smith@email.com	Visitor	Institution	Institution	aug	1	yes	yes	yes	yes	yes	yes
3	02/08/2022	John Smith	john.smith@email.com	Visitor	Institution	Institution	aug	3	yes	yes	yes	yes	yes	yes
4	02/08/2022	John Smith	john.smith@email.com	Visitor	Institution	Institution	aug	3	yes	yes	yes	yes	yes	yes
5	02/08/2022	John Smith	john.smith@email.com	Visitor	Institution	Institution	aug	3	yes	yes	yes	yes	yes	no
6	02/08/2022	John Smith	john.smith@email.com	Visitor	Institution	Institution	aug	3	yes	yes	yes	yes	yes	yes
7	02/08/2022	John Smith	john.smith@email.com	Visitor	Institution	Institution	aug	3	yes	yes	yes	yes	yes	no
8	02/08/2022	John Smith	john.smith@email.com	Visitor	Institution	Institution	aug	3	yes	yes	yes	yes	yes	yes
9	02/08/2022	John Smith	john.smith@email.com	Visitor	Institution	Institution	aug	3	yes	yes	yes	yes	yes	yes
10	02/08/2022	John Smith	john.smith@email.com	Visitor	Institution	Institution	aug	2	yes	yes	yes	yes	yes	yes
11	02/08/2022	John Smith	john.smith@email.com	Visitor	Institution	Institution	aug	3	yes	yes	yes	yes	yes	no
12	02/08/2022	John Smith	john.smith@email.com	Visitor	Institution	Institution	aug	3	yes	yes	yes	yes	yes	no
13	02/08/2022	John Smith	john.smith@email.com	Visitor	Institution	Institution	aug	2	yes	yes	yes	yes	yes	yes
14	02/08/2022	John Smith	john.smith@email.com	Visitor	Institution	Institution	aug	3	yes	yes	yes	yes	yes	yes
15	02/08/2022	John Smith	john.smith@email.com	Visitor	Institution	Institution	aug	3	yes	yes	yes	yes	yes	yes
New Visitors

 

[Z51]

This macro should work based on your sample data. Paste this code into the 'New Visitors' code page in VBA Editor, and it will get triggered whenever you make a change on that sheet. It checks to see that the change occurred in Column N, and that the new value is "yes" (or "Yes" or "YES" or "YeS", etc. since the code converts the value to UCase to compare to "YES".

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("N:N")) Is Nothing Then
    If UCase(Target.Value) = "YES" Then
        Dim nextrow As Long
        nextrow = Sheets("Completed Visits").Range("A" & Rows.Count).End(xlUp).Row + 1
        
        Application.EnableEvents = False
        Sheets("Completed Visits").Range("A" & nextrow).Resize(1, 14).Value = Sheets("New Visitors").Range("A" & Target.Row).Resize(1, 14).Value
        Sheets("New Visitors").Range("A" & Target.Row).EntireRow.Delete
        Application.EnableEvents = True
    End If
End If
End Sub