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,


  • 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.


    A B

    Hi Abby

    Hello Roy

    Hi John

    Hi Jane

    And the keyword = "Hi"

    In Destination sheet it should have copied:





    (And destination sheet has a table).

    Thanks for the help!


  • 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:


    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.


  • hi Roy,

    Since it uses the offset to copy and not paste, how can I modify it so when it pastes it only pastes the value and not the format?


  • 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.

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!