filtering a column and pasting the results

[apor]

i have one column, column a, of which i want to copy the rows that contain the numbers 3171 and paste all of these rows into a new sheet.

i used this code before, and now that im trying to use it again its not working. all thats happening is that the first row with 3171 gets pasted into 300 rows of column a of sheet 2

Sub Paste3171
With Sheet1
.AutoFilterMode = False
With .Range("A1:A300")
.AutoFilter Field:=1, Criteria1:="3171"
.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet2.Range("A1:A300")
End With
End With

also, i would like to not just select a1:a300 in both sheets, but preferrably the entire column because i often have over 300 values

 

[kevin9999]

Try the following

Sub copy3171()
    With Sheet1.Cells(1, 1).CurrentRegion
        .AutoFilter 1, "3171"
        .Copy Sheet2.Cells(1)
        .AutoFilter
    End With
End Sub

 

[apor]

Try the following

Sub copy3171()
    With Sheet1.Cells(1, 1).CurrentRegion
        .AutoFilter 1, "3171"
        .Copy Sheet2.Cells(1)
        .AutoFilter
    End With
End Sub

hi kevin, all this is doing is pasting the cell a1 into my second sheet

 

[kevin9999]

OK, try this

Sub copy3171()
    With Sheet1.Range("A:A")
        .AutoFilter 1, "3171"
        .Copy Sheet2.Cells(1)
        .AutoFilter
    End With
End Sub

 

[apor]

OK, try this

Sub copy3171()
    With Sheet1.Range("A:A")
        .AutoFilter 1, "3171"
        .Copy Sheet2.Cells(1)
        .AutoFilter
    End With
End Sub

i am still only getting the first cell of column a in sheet 1 in column a of sheet 2, all other cells of sheet 1 with value 3171 arent showing up

 

[kevin9999]

OK, I can't go any further unless you post your actual data. Both codes suggested work for me, but I'm only guessing what your data looks like.

 

[apor]

111 3171
222 3171
333 1000
444 1000
555 3171

i would want the resulting column to look like this:
111 3171
222 3171
555 3171

 

[apor]

OK, I can't go any further unless you post your actual data. Both codes suggested work for me, but I'm only guessing what your data looks like.

this data is all i one column

 

[kevin9999]

I don't know how your original code could have worked with the data you posted, however, this should work with a wildcard:

Sub copy3171()
    With Sheet1.Cells(1, 1).CurrentRegion
        .AutoFilter 1, "*3171"
        .Copy Sheet2.Cells(1)
        .AutoFilter
    End With
End Sub

 

[apor]

I don't know how your original code could have worked with the data you posted, however, this should work with a wildcard:

Sub copy3171()
    With Sheet1.Cells(1, 1).CurrentRegion
        .AutoFilter 1, "*3171"
        .Copy Sheet2.Cells(1)
        .AutoFilter
    End With
End Sub

thank you so much for you help! it worked perfectly
however, sometimes i have other data in sheet 1 that i do not want to filter. how do i specify this code to just column A in sheet 1? i also named this column Table4, if that helps