Create a macro to find a value in a table, copy the entire row and insert it to a new sheet?
Results 1 to 3 of 3

Thread: Create a macro to find a value in a table, copy the entire row and insert it to a new sheet?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Create a macro to find a value in a table, copy the entire row and insert it to a new sheet?

    I'm looking to create a macro to look up a given value in a table, cut the entire row that the value is in, and paste it at the bottom of a different sheet.

    The lookup value is located at cell B14 of Sheet1, it will later be tied to a userform. The table with the list of values in on Sheet2, and the lookup value will always be found in column A of the table. The row lengths vary from 4 cells up to 11. It will be replacing the row onto a table on sheet 3, where I will alphabetize it by column A.

    Essentially, Sheet2 contains active items and sheet 3 contains retired items, but it is useful to still have them for reference. Could anyone help with this?
    0 0
     

  2. #2
    Board Regular
    Join Date
    Aug 2003
    Location
    England
    Posts
    5,611
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Create a macro to find a value in a table, copy the entire row and insert it to a new sheet?

    Hello,

    Does this code work as expected?

    Code:
    Sub MOVE_TO_SHEET_3()
        Application.ScreenUpdating = False
        With Sheets("Sheet2")
            For MY_ROWS = .Range("A" & .Rows.Count).End(xlUp).Row To 1 Step -1
                If .Range("A" & MY_ROWS).Value = Sheets("Sheet1").Range("B14").Value Then
                    .Rows(MY_ROWS).Copy
                    Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
                    .Rows(MY_ROWS).Delete
                End If
            Next MY_ROWS
        End With
        Application.ScreenUpdating = True
    End Sub
    -------------------------
    Hope this is helpful.
    -------------------------

    Have been away from the forum for quite a while, but am now back.
    0 0
     

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,836
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Create a macro to find a value in a table, copy the entire row and insert it to a new sheet?

    Duplicate https://www.mrexcel.com/forum/excel-...ml#post5332158

    Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
    Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

    As you have said the code in the other thread is working, I will close this thread
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10
    0 0
     

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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