Autofilter and the Copy result Into another sheet

andyb16 · Aug 10th 2013

Hi All,

Attached is indicative data : --> forum.ozgrid.com/index.php?attachment/55654/

My aim is to apply an autofilter on "Geography" so that only employees with APAC and Europe are selected.

Then I want to copy the filtered result and paste it in tab "Expected Result" cell A5.

Is there any code to achieve this?

Thanks.

andyb16 · Aug 10th 2013

Re: Autofilter and the Copy result Into another sheet

One additional info - This is going to be a part of a larger macro.

Hence need a code.

andyb16 · Aug 10th 2013

Re: Autofilter and the Copy result Into another sheet

Ok...i was able to record a macro.

However, I am worried about the "AutoFilter Field:=5" bit. This field may move from month to month as raw data columns get added/deleted. Hence macro will fail as it will filter on incorrent column. Is there any way to tie it with the heading "Geography".

Request your help. Thanks.

Code

Sub AUTOFILT()
'
' AUTOFILT Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$F$5").AutoFilter Field:=5, Criteria1:=Array( _
        "APAC", "EUROPE"), Operator:=xlFilterValues
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
End Sub

Display More

jindon · Aug 10th 2013

Re: Autofilter and the Copy result Into another sheet

Try this

Code

Sub test()
    Dim x
    With Sheets("Original").Cells(1).CurrentRegion
        .Parent.AutoFilterMode = False
        x = Application.Match("GEOGRAPHY", .Rows(1), 0)
        If IsNumeric(x) Then
            .AutoFilter x, Array("APAC", "EUROPE"), Operator:=xlFilterValues
            .Copy Sheets("expected result").Range("a5")
            .AutoFilter
        End If
    End With
End Sub

Display More

andyb16 · Aug 10th 2013

Re: Autofilter and the Copy result Into another sheet

Thanks jindon!

Worked perfectly!
:thanx:

Autofilter and the Copy result Into another sheet

Participate now!

Share