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:

Sub Testmacro1()

Dim c As Range
Dim Source As Worksheet
Dim Dest As Worksheet


Set Source = ActiveWorkbook.Worksheets("SourceSht")
Set Dest = ActiveWorkbook.Worksheets("DestSht")

For Each c In Source.Range("A1:A" & Source.Cells(Rows.Count, 1).End(xlUp).Row)
   If c = "Header" Then
      c.EntireRow.Copy
      Dest.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
  
   End If
Next c

Application.CutCopyMode = False

End Sub

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)

  Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet

    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Source")
    Set Target = ActiveWorkbook.Worksheets("Dest")

    j = 2
    For Each c In Source.Range("A1:A200")
        If c = "Task Header" Then
           Source.Rows(c.Row).Copy
           Target.Rows(j).PasteSpecial Paste:=xlPasteValues
           
           j = j + 1
        End If
    Next c

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:

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

hi Roy,

Here is a sample. I would love to see how I can make this more efficient without loops.

Sample.xlsm
Thanks so much for your help,

CS

Wow, thank you. It works.

So you used Autofilter instead and then copied those over? Is that correct?

Thank you again.

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?

Thanks!

Thank you so much for the help!