Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Transfer new row under headings

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

    Transfer new row under headings



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

    Hi Audrey
    This code is ideal for a project I am working on. Is it possible to transfer the data to Row 2 under the headings rather than the next available Row.
    Kind Regards

    Edit Mod thread related to https://www.excelguru.ca/forums/show...-on-cell-value
    Last edited by Pecoflyer; 2019-06-16 at 07:41 AM. Reason: Removed unnecessary quote - added link

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

    I assume that you were actually referring to me rather than the OP. The code modified as follows may actually suit you better:-


    Code:
    Sub Test()
    
    Application.ScreenUpdating = False
    
    With Sheet1.[A1].CurrentRegion
            .AutoFilter 9, "Closed"
            .Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
            .Offset(1).EntireRow.Delete
            .AutoFilter
    End With
    
    Application.ScreenUpdating = True
    
    End Sub
    Test it in a copy of your actual workbook first and let us know how it goes.

    BTW. You may want to change your nick-name. Its not a good idea to use your email as a nick-name.

    I hope that this helps.

    Cheerio,
    vcoolio.

  3. #3
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,667
    Articles
    0
    Excel Version
    2010 on Xubuntu
    @treacy
    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - I did it for you this time
    Thank you Ken for this secure forum.

  4. #4
    Seeker treacy5@hotmail.com's Avatar
    Join Date
    Sep 2018
    Posts
    8
    Articles
    0
    Excel Version
    Excel 2016
    Yes, I was referring to you vcoolio. My apologies. Thanks so much for replying.

  5. #5
    Seeker treacy5@hotmail.com's Avatar
    Join Date
    Sep 2018
    Posts
    8
    Articles
    0
    Excel Version
    Excel 2016
    My apologies. Will do.

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

    You're welcome. The obvious question now is: Did the code in post #2 work for you?

    Cheerio,
    vcoolio.

  7. #7
    Seeker treacy5@hotmail.com's Avatar
    Join Date
    Sep 2018
    Posts
    8
    Articles
    0
    Excel Version
    Excel 2016
    No. I changed some row and column references to suit my project. But it is not working. Can I upload the file here if u had time to look at it. I am new to this site.

  8. #8
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,248
    Articles
    0
    Excel Version
    Office 365 Subscription
    EDIT post > Go Advanced > Manage Attachments
    Ali
    Enthusiastic self-taught user of MS Excel!

  9. #9
    Seeker treacy5@hotmail.com's Avatar
    Join Date
    Sep 2018
    Posts
    8
    Articles
    0
    Excel Version
    Excel 2016
    When "Yes" is entered in column header Received in Sheet 2, I would like to move that row into Sheet 1. Put the most recent records that are moved into Row 2 of Sheet 1.VCoolioNew.xlsm

  10. #10
    Acolyte vcoolio's Avatar
    Join Date
    Jan 2016
    Posts
    27
    Articles
    0
    Excel Version
    2016
    Hello Treacy,

    Just a slight modification as follows:-

    Code:
    Sub Test()
    
    Application.ScreenUpdating = False
    
    With Sheet1.[A1].CurrentRegion  '----> Sheet1 is the sheet code for the sheet name "Sheet2".
            .AutoFilter 2, "Yes"
             .Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2) '  ----> Sheet2 is the sheet code for the sheet name "Sheet1".
            .Offset(1).EntireRow.Delete
            .AutoFilter
    End With
    
    Sheet2.Columns.AutoFit
    
    Application.ScreenUpdating = True
    
    End Sub
    The sheet code/sheet name may have confused you.

    I noticed that you placed the code into the worksheet module. This code is not an event code and needs to be placed into a standard module and assigned to a button. Were you wanting to use an event code (no buttons)? An event code would immediately transfer the data as soon as "Yes" is placed into any cell in Column B.

    I hope that this helps.

    Cheerio,
    vcoolio.

Page 1 of 2 1 2 LastLast

Posting Permissions

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