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

%3CLINGO-SUB%20id%3D%22lingo-sub-1851720%22%20slang%3D%22en-US%22%3EExcel%20VBA%3A%20Filter%2C%20cut%2C%20and%20paste%20to%20another%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1851720%22%20slang%3D%22en-US%22%3E%3CP%3E1st%20sheet%20named%20%3CSTRONG%3Esrc%3C%2FSTRONG%3E%20while%20the%202nd%20one%20is%20%3CSTRONG%3Edst%3C%2FSTRONG%3E%20which%20is%20an%20empty%20sheet%20at%20the%20moment.%3C%2FP%3E%3CP%3EMy%20plan%20is%20to%20filter%20string%20%3CSTRONG%3Ex%3C%2FSTRONG%3E%20in%20column%20B%2C%20cut%20it%20and%20paste%20it%20to%202nd%20sheet%20%3CSTRONG%3Edst%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22NcmPm%22%20style%3D%22width%3A%20411px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F231342i481A7DDBE03F970E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22NcmPm%22%20alt%3D%22NcmPm%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EVBA%20code%3C%2FP%3E%3CPRE%3E%3CSPAN%20class%3D%22hljs-keyword%22%3ESub%3C%2FSPAN%3E%20filter_copy_paste()%0A%0A%3CSPAN%20class%3D%22hljs-keyword%22%3EWith%3C%2FSPAN%3E%20Sheets(%3CSPAN%20class%3D%22hljs-string%22%3E%22src%22%3C%2FSPAN%3E)%0A%20%20%20%20.Range(%3CSPAN%20class%3D%22hljs-string%22%3E%22A1%22%3C%2FSPAN%3E).AutoFilter%20Field%3A%3D%3CSPAN%20class%3D%22hljs-number%22%3E2%3C%2FSPAN%3E%2C%20Criteria1%3A%3D%3CSPAN%20class%3D%22hljs-string%22%3E%22x%22%3C%2FSPAN%3E%0A%20%20%20%20%3CSPAN%20class%3D%22hljs-keyword%22%3EWith%3C%2FSPAN%3E%20.AutoFilter.Range%0A%20%20%20%20%20%20%20%20%3CSPAN%20class%3D%22hljs-keyword%22%3EWith%3C%2FSPAN%3E%20.SpecialCells(xlCellTypeVisible).EntireRow%0A%20%20%20%20%20%20%20%20%20%20%20%20.Copy%0A%20%20%20%20%20%20%20%20%20%20%20%20%3CSPAN%20class%3D%22hljs-keyword%22%3EWith%3C%2FSPAN%3E%20Sheets(%3CSPAN%20class%3D%22hljs-string%22%3E%22dst%22%3C%2FSPAN%3E)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.Paste%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.%5BA1%5D.%3CSPAN%20class%3D%22hljs-keyword%22%3ESelect%3C%2FSPAN%3E%0A%20%20%20%20%20%20%20%20%20%20%20%20%3CSPAN%20class%3D%22hljs-keyword%22%3EEnd%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22hljs-keyword%22%3EWith%3C%2FSPAN%3E%0A%20%20%20%20%20%20%20%20%3CSPAN%20class%3D%22hljs-keyword%22%3EEnd%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22hljs-keyword%22%3EWith%3C%2FSPAN%3E%0A%20%20%20%20%3CSPAN%20class%3D%22hljs-keyword%22%3EEnd%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22hljs-keyword%22%3EWith%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22hljs-keyword%22%3EEnd%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22hljs-keyword%22%3EWith%3C%2FSPAN%3E%0A%0A%3CSPAN%20class%3D%22hljs-keyword%22%3EEnd%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22hljs-keyword%22%3ESub%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%3CBR%20%2F%3EHowever%2C%20there%20is%20an%20error%20when%20I%20run%20it%20and%20when%20I%20hit%20Debug%2C%20it%20highlights%20line%20number%205%20which%20is%20%3CSTRONG%3EWith%20.AutoFilter.Range%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22irFpw%22%20style%3D%22width%3A%20584px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F231344i391232806636F8D5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22irFpw%22%20alt%3D%22irFpw%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20wrong%20in%20this%20code%20and%20what%20should%20I%20do%20to%20fix%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EDesired%20output%20in%201st%20sheet%20src%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%229v8rA%22%20style%3D%22width%3A%20409px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F231343i82F8AAD8400B0000%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%229v8rA%22%20alt%3D%229v8rA%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EDesired%20output%20in%202nd%20sheet%20%22dst%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mWqMW%22%20style%3D%22width%3A%20407px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F231345i831897874F979BC4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22mWqMW%22%20alt%3D%22mWqMW%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1851720%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1852553%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%3A%20Filter%2C%20cut%2C%20and%20paste%20to%20another%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1852553%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F839021%22%20target%3D%22_blank%22%3E%4011392%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20code%20runs%20without%20error%20when%20I%20try%20it%2C%20but%20see%20if%20this%20version%20works%20for%20you%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20filter_copy_paste()%0A%20%20%20%20With%20Sheets(%22src%22).Range(%22A1%22).CurrentRegion%0A%20%20%20%20%20%20%20%20.AutoFilter%20Field%3A%3D2%2C%20Criteria1%3A%3D%22x%22%0A%20%20%20%20%20%20%20%20.SpecialCells(xlCellTypeVisible).EntireRow.Copy%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20Destination%3A%3DSheets(%22dst%22).Range(%22A1%22)%0A%20%20%20%20End%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New 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