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

Copper Contributor

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

 

NcmPm

 

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

 

irFpw

 

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

 

Desired output in 1st sheet src

9v8rA

 

Desired output in 2nd sheet "dst"

mWqMW

1 Reply

@11392 

The code runs without error when I try it, but see if this version works for you:

Sub filter_copy_paste()
    With Sheets("src").Range("A1").CurrentRegion
        .AutoFilter Field:=2, Criteria1:="x"
        .SpecialCells(xlCellTypeVisible).EntireRow.Copy _
            Destination:=Sheets("dst").Range("A1")
    End With
End Sub