Results 1 to 4 of 4

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

  1. #1
    Neophyte sallyr's Avatar
    Join Date
    Sep 2020
    Posts
    2
    Articles
    0
    Excel Version
    2010

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!
    Attached Files Attached Files

  2. #2
    Acolyte vcoolio's Avatar
    Join Date
    Jan 2016
    Posts
    29
    Articles
    0
    Excel Version
    2016
    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.

  3. #3
    Neophyte sallyr's Avatar
    Join Date
    Sep 2020
    Posts
    2
    Articles
    0
    Excel Version
    2010
    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.

  4. #4
    Acolyte vcoolio's Avatar
    Join Date
    Jan 2016
    Posts
    29
    Articles
    0
    Excel Version
    2016
    Hello Sally,

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

    Cheerio,
    vcoolio.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •