How to add data from specific rows of a table to another table

Question

how to copy a group of rows if cells in the first column equal a certain value.

this is the code i found online, but can't seem to get it to work, i think its because I've formated data on the sheet as a table.

Private Sub CommandButton1_Click()
    a = Worksheets("inbd").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To a
        If Worksheets("inbd").Cells(i, 3).Value = 76 Then
            Worksheets("inbd").Rows(i).Copy
            Worksheets("sheet2").Activate
            b = Worksheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("sheet2").Cells(b + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("sheet1").Activate
        End If
    Next

    Application.CutCopyMode = False
    ThisWorkbook.Worksheets("inbd").Cells(1, 1).Select
End Sub

Example of how table on "inbd" looks:

Also do you know that instead of looping, you can use Autofilter to copy all rows where `Cells(i, 3).Value = 76` in one go? You may want to see [THIS](https://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s/11633207#11633207) — Siddharth Rout, Aug 03 '18 at 07:14
Just a note but this might be of interest too: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Aug 03 '18 at 07:44
well the macro does nothing it jumps from If Worksheets("inbd").Cells(i, 3).Value = 76 Then to the end — Joe Royson, Aug 03 '18 at 08:06
Should i replace the (i, 3) <= with the column? if so, how do i do that (Note: I'm a complete beginner in VBA, I've done one project so far, self-learner, so bear with me) — Joe Royson, Aug 03 '18 at 08:12

Xabier · Accepted Answer · 2018-08-03T09:09:49.270

How about the following, this will filter column A with the relevant criteria and copy the filtered rows into Sheet2, you will need to amend the range as I've used Column A to N, please also bear in mind in the code below I'm filtering Column A to find the value 76, whereas on your original code you were filtering Column C as your code Cells(i, 3).Value = 76 where 3 is the Column number:

Sub foo()
Dim ws As Worksheet: Set ws = Sheets("inbd")
Dim wsDestination As Worksheet: Set wsDestination = Sheets("Sheet2")
'declare and set your worksheet, amend as required
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'get the last row with data on Column A

ws.Range("A1:N" & LastRow).AutoFilter Field:=1, Criteria1:="76"
'filter data on Column 1 (A), change the Field number from 1 to the column number you wish to filter
ws.Range("A2:N" & LastRow).SpecialCells(xlCellTypeVisible).Copy
'copy filtered results
DestinationRow = wsDestination.Cells(wsDestination.Rows.Count, "A").End(xlUp).Row + 1
'get the destination row
wsDestination.Range("A" & DestinationRow).PasteSpecial xlPasteAll
'paste into Sheet2
Application.CutCopyMode = False
'deselect the copied rows
ws.Range("A1:N" & LastRow).AutoFilter Field:=1
'remove filter
End Sub

Thank you, seems like a good starting point, I have to make it do a lot of things, but doing it step by step — Joe Royson, Aug 03 '18 at 11:03

How to add data from specific rows of a table to another table

1 Answers1