Complete novice - how to move a row to another sheet - patient seen

sallyr

New member
Joined
Sep 17, 2020
Messages
2
Reaction score
0
Points
0
Excel Version(s)
2010
Please help.

I have read lots of forums and still can't work out how to get the code to work.

Please see attached worksheet for patient referrals. I would like to move patients once seen into a sheet 2 (seen).

Please can someone help!
 

Attachments

  • PT referrals.xlsx
    10.2 KB · Views: 10
Hello Sally,

You could use a Worksheet_Change event code to do the task for you as follows:-

Code:
Option Compare Text

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
    
    If Target.Value = "Yes" Then
         Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
         Target.EntireRow.Delete
         Sheet2.Columns.AutoFit
    End If

End Sub

Each time that "Yes" is typed into a cell in Column G and you click away or press enter or down arrow, the relevant entire row will be transferred to the "Seen" sheet. The transferred row of data will be deleted from the "Referrals" sheet.
If you create a data validation list for each cell in Column G (Yes or No), the row of data will be transferred over immediately upon selection of "Yes".

I'm assuming that your sample workbook is an exact replica of your actual workbook.

To implement this code:-
- Right click on the "Referrals" sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Test the code in a copy of your actual workbook first.

I hope that this helps.

Cheerio,
vcoolio.
 
Thank you so much vcoolio, it has worked!!

I am very excited to share this with my colleagues too, small things make a great bit of difference.
 
Hello Sally,

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

Cheerio,
vcoolio.
 
Back
Top