Copy Paste going outside the table

  • hi there,


    It's late and I can't think anymore.

    I am trying to look for a keyword in Sheet1 Column A and if found, I need it to copy that row and paste it into a table in Sheet2.

    The problem is it keeps pasting it outside the table in sheet 2. I'm wondering if someone can help me troubleshoot why.

    So what it is doing is, if I have 2 rows in Sheet 2 (row 1 being the header row and row 2 the first row of the table), it pastes it into Row 3 which isn't a part of the table. I need it to paste into row 2.



    Here is the code:


    Also, is there a way to only get it to copy Column B and not the entire row?


    Thanks in advance,

    CS

  • hi Roy,


    Thanks for your suggestions. Wouldn't it need to loop through each row in the Source sheet to find that keyword and then copy though?

    I'd like it to copy the cell in B that matches a keyword in column A, but there could be 1 or many of those keywords in column A.

    So:


    A B

    Hi Abby

    Hello Roy

    Hi John

    Hi Jane


    And the keyword = "Hi"

    In Destination sheet it should have copied:


    B

    Abby

    John

    Jane


    (And destination sheet has a table).


    Thanks for the help!

    CS

  • This seems to work (it pastes it into the second row)


    I just have to figure out why it is not expanding the table when it pastes the data.

    Also, right now it's pasting columns A and B still.

  • I think I got it.


    I added this line to expand the table:


    Code
    1. Target.ListObjects("Table4").ListRows.Add


    It adds an extra blank row but I just use a delete row line to take care of that.


    If anyone has a more efficient solution let me know.


    Thanks

  • AutoFilter is much more efficient than looping.


    Try this


    Changing this line would make your code not copy the header, but it would be slower than mine.


    Code
    1. For Each c In Source.Range("A2:A" & Source.Cells(Rows.Count, 1).End(xlUp).Row)
  • Pleased to help.


    Post back if you need further help.


    Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.

  • royUK

    Closed the thread.