Forum Discussion

11392's avatar
11392
Copper Contributor
Nov 04, 2020

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

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"

Macros and VBA

1 Reply

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 04, 2020

    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

Resources