Page 2 of 2 FirstFirst 1 2
Results 11 to 16 of 16

Thread: Transfer new row under headings

  1. #11
    Seeker treacy5@hotmail.com's Avatar
    Join Date
    Sep 2018
    Posts
    8
    Articles
    0
    Excel Version
    Excel 2016


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

    I would prefer an Event code.

  2. #12
    Acolyte vcoolio's Avatar
    Join Date
    Jan 2016
    Posts
    27
    Articles
    0
    Excel Version
    2016
    Hi Treacy,

    Try the following event code:-


    Code:
    Option Compare Text
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Intersect(Target, Columns(2)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Value = vbNullString Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Sheet2.Rows(2).EntireRow.Insert
    
    If Target.Value = "Yes" Then
    Target.EntireRow.Copy Sheet2.[A2]
    Target.EntireRow.Delete
    End If
    
    Application.ScreenUpdating = True
    
    End Sub
    You need to make sure that "Yes" is the last entry in each row as the cells in Column B are the trigger for the event code.
    Once you enter "Yes" in any cell in Column B then click away (or press enter or down arrow) the code will transfer the relevant row of data to the destination sheet.

    To implement this code:-

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

    Test it in a copy of your workbook first.

    I hope that this helps.

    Cheerio,
    vcoolio.
    Last edited by vcoolio; 2019-06-16 at 12:27 PM.

  3. #13
    Seeker treacy5@hotmail.com's Avatar
    Join Date
    Sep 2018
    Posts
    8
    Articles
    0
    Excel Version
    Excel 2016
    That is brilliant. Thank you for being so generous with your time. It deletes the row and places the most recent row where I want. You are a gentleman. Thanks again

  4. #14
    Acolyte vcoolio's Avatar
    Join Date
    Jan 2016
    Posts
    27
    Articles
    0
    Excel Version
    2016
    Hi Treacy,

    Wow! Thanks for the kind words.

    You're welcome. I'm glad to have been able to assist and I'm glad that it now all works for you.

    (remember to change your nick-name!!)

    Cheerio,
    vcoolio.

  5. #15
    Seeker treacy5@hotmail.com's Avatar
    Join Date
    Sep 2018
    Posts
    8
    Articles
    0
    Excel Version
    Excel 2016
    You mentioned that Yes in Column 2 had to be entered last. It would make more sense to make the Date Received (Column C) the trigger and Delete the Received (Column B) altogether. Would that require much alteration of the code. Once a date is entered in the Date Received Column, that means a reply has been received anyway.

  6. #16
    Acolyte vcoolio's Avatar
    Join Date
    Jan 2016
    Posts
    27
    Articles
    0
    Excel Version
    2016
    Hi Treacy,

    Amend the code as follows:-


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Intersect(Target, Columns(3)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Value = vbNullString Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Sheet2.Rows(2).EntireRow.Insert
    
    If IsDate(Target.Value) Then
    Target.EntireRow.Copy Sheet2.[A2]
    Target.EntireRow.Delete
    End If
    
    Application.ScreenUpdating = True
    
    End Sub
    Just be careful though because if you delete Column B, Column C (Date Received) will then become Column B or Column2. Hence, in this line of code:-

    Code:
    If Intersect(Target, Columns(3)) Is Nothing Then Exit Sub
    you may still need to leave the 3 as 2.

    I hope that this helps.

    Cheerio,
    vcoolio.

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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