I am looking for vba code filter data to copy and paste in same sheet

[santoshloka]

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


For i = 2 To a
If Worksheets("sheet1").Cells(i, 6).Value = "x" Then


Worksheets("Sheet1").Rows(i).Copy
Worksheets("Sheet1").Cells.field = 10
b = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("sheet1").Activate
End If
Next i
Application.CutCopyMode = False


ThisWorkbook.Worksheets("sheet1").Cells(1, 1).Select
End Sub

 

[mumps]

Try:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim bottomF As Long
    bottomF = Sheets("Sheet1").Range("F" & Rows.Count).End(xlUp).Row
    Sheets("Sheet1").Range("F1:F" & bottomF).AutoFilter Field:=1, Criteria1:="x"
    Sheets("Sheet1").Range("F2:F" & bottomF).SpecialCells(xlCellTypeVisible).EntireRow.Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    If Sheets("Sheet1").FilterMode Then Sheets("Sheet1").ShowAllData
    Application.ScreenUpdating = True
End Sub