• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter a column <> criteria and copy to next sheet

M

Mr.Karr

Member
Hi,

Can anyone pls provide a vba snippet to filter a column <> defined criteria (except "BIF*" and SR-*") and copy to next sheet.

Pls see the attached file.

THanks in advance,
Karthik
 

Attachments

  • advanced filter.xlsx
    10.2 KB · Views: 5
Is this...

Code: 
Sub Macro1()
ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:="<>BIF*", Operator:=xlAnd, Criteria2:="<>SR-*"
End Sub
 
Mr.Karr said:
yes, it works like a charm.
Can we clear content from next sheet & paste them directly to there ?
Click to expand...

Opps. I just over looped second req.

Check this & let me know!

Code: 
Option Explicit

Sub Macro2()
Application.ScreenUpdating = False
Sheet2.UsedRange.Clear
With Sheet1
    .UsedRange.AutoFilter Field:=1, Criteria1:="<>BIF*", Operator:=xlAnd, Criteria2:="<>SR-*"
    .AutoFilter.Range.Copy Sheet2.[A1]
    .AutoFilterMode = False
End With
Sheet2.Columns.AutoFit
Application.ScreenUpdating = True
End Sub
 
Mr.Karr said:
@Deepak .
If I have actual data starting from A14, how can I filter from that part?
Can we modify this part please?
Click to expand...

To get it work hassle free pls uplaod a sample file or try this..

Code: 
Option Explicit

Sub Macro3()
Dim myRng As Range

Application.ScreenUpdating = False

Set myRng = ActiveSheet.Range("A14:B20")
Sheet2.UsedRange.Clear

With myRng
    .AutoFilter Field:=1, Criteria1:="<>BIF*", Operator:=xlAnd, Criteria2:="<>SR-*"
    .AutoFilter.Range.Copy Sheet2.[A1]
    .AutoFilterMode = False
End With
Sheet2.Columns.AutoFit
Application.ScreenUpdating = True
End Sub
 
Please see the attached file. I have included the code as well
 

Attachments

  • Custom filter & paste.xlsm
    18.5 KB · Views: 3
Mr.Karr said:
Please see the attached file. I have included the code as well
Click to expand...


Code: 
Sub Macro4()
Application.ScreenUpdating = False

With Range("A13").CurrentRegion
    .AutoFilter Field:=1, Criteria1:="<>BIF*", Operator:=xlAnd, Criteria2:="<>SR-*"
    .SpecialCells(xlCellTypeVisible).Copy Sheet2.[A19]
    '.AutoFilter.Range.Resize(, 1).Copy Sheet2.[A1]
    ActiveSheet.AutoFilterMode = False
End With

Sheet2.Columns.AutoFit
Application.ScreenUpdating = True
End Sub
 
Mr.Karr said:
@Deepak Please note, data to be pasted only Column A but this is pasting everything. Pls advise
Click to expand...

Code: 
Sub Macro5()
Application.ScreenUpdating = False

Sheet2.[A19].CurrentRegion.Cells.Clear

With Range("A13").CurrentRegion.Resize(, 1)
    .AutoFilter Field:=1, Criteria1:="<>BIF*", Operator:=xlAnd, Criteria2:="<>SR-*"
    .SpecialCells(xlCellTypeConstants).Copy Sheet2.[A19]
  ActiveSheet.AutoFilterMode = False
End With

Sheet2.Columns.AutoFit
Application.ScreenUpdating = True
End Sub
 
You must log in or register to reply here.
Back
Top