Excel VBA: Filter, cut, and paste to another sheet

[11392]

1st sheet named src while the 2nd one is dst which is an empty sheet at the moment.

My plan is to filter string x in column B, cut it and paste it to 2nd sheet dst



VBA code

Sub filter_copy_paste()

With Sheets("src")
    .Range("A1").AutoFilter Field:=2, Criteria1:="x"
    With .AutoFilter.Range
        With .SpecialCells(xlCellTypeVisible).EntireRow
            .Copy
            With Sheets("dst")
                .Paste
                .[A1].Select
            End With
        End With
    End With
End With

End Sub

However, there is an error when I run it and when I hit Debug, it highlights line number 5 which is With .AutoFilter.Range

What wrong in this code and what should I do to fix it?

Desired output in 1st sheet "src"



Desired output in 2nd sheet "dst"

 

[smozgur]

Try this.

Note: When the data is copied to the destination worksheet, we use the entire AutoFilter range as you did.
However, to remove the copied rows from the sheet (I assume you want them to be removed), then we should exclude the header row, so .Areas(2) is used for that purpose.

Sub filter_copy_paste()
    With Sheets("src")
        .Range("A1").AutoFilter Field:=2, Criteria1:="x"
        With .AutoFilter.Range.SpecialCells(xlCellTypeVisible)
            With .EntireRow
                .Copy Sheets("dst").Range("A1")
            End With
            With .Areas(2).EntireRow
                .Delete (xlShiftUp)
            End With
        End With
        .Range("A1").AutoFilter
    End With
    Application.Goto Sheets("dst").Range("A1")
End Sub

 

[MARK858]

Just a slightly different method. Btw do either of the responses also answer your question

Excel VBA to filter column, cut the content and paste it to the other sheets

This is the Sheet1 and has been renamed it to s1 The VBA script below creates 2 new sheets, and name it as Task XYZ and Group JKL. This script also filter the 2nd field (column B), and look for string specific string, let say x. Finally it copies the first row on s1 and paste it to another...

www.mrexcel.com

Sub filter_copy_paste()

    With Sheets("src")
        .Range("A1").AutoFilter Field:=2, Criteria1:="x"
        With .AutoFilter.Range
            .EntireRow.Copy Sheets("dst").Range("A1")
            .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With
    Application.Goto Sheets("dst").Range("A1")
End Sub