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]

Do not want to filter, or do not want to copy?

 

[apor]

Do not want to filter, or do not want to copy?

both, i figured it out using this code

Sub copy3171()
Set range_to_filter = Range("Table4")
range_to_filter.AutoFilter 1, "*3171"
range_to_filter.Copy Sheet2.Cells(1)
range_to_filter.AutoFilter
End Sub


thank you for your help!

 

[kevin9999]

You're welcome and well done working it out - it's always more satisfying