ANmAdvancedFilter

Filter table inside Excel the fast way (using advanced filter) into another sheet.
This sub assumes all these sheets are presented in the same workbook.
ShSource, ShFilter and ShCopyTo.
Will have another version with more flexible options.
Used and worked perfect.

Make sure you understand AdvancedFilter in Excel to apply filter range, data range and copy to range. This sub uses Excel AdvancedFilter.

Trick to use ANmAdvancedFilter below
    To filter large table on multiple columns at the same time having each column to be one of many values
    Use .... multiple sheets to filter once per column
    Like we did in Reiporter tool

CodeFunctionName

What is this?

Public

Tested

Original Work

Function ANmAdvancedFilter(ShSource, RSource, ShFilter, RFilter, ShCopyTo, Optional RCopyTo = "A1", Optional WB = "This")
    ' Copy table from a sheet into another after applying filter and returns number of rows
    '    aka AdvancedFilter
    ' +++ For now, all should be within same workbook +++
    ' RSource = Range of source, full range
    ' ShSource= Sheet name of source table
    ' RFilter = Range having filter, 1st row should have similar values from RSource
    ' ShFilter= sheet having RFilter
    ' RCopyTo = Range to copy filtered output to, expect to have entire sheet cleared and filled again
    ' ShCopyTo= Sheet to copy to
    '
    If WB = "This" Then WB = ThisWorkbook.Name
    If WB = "Active" Then WB = ActiveWorkbook.Name

    Workbooks(WB).Worksheets(ShSource).Range(RSource).AdvancedFilter xlFilterCopy, Workbooks(WB).Worksheets(ShFilter).Range(RFilter), Workbooks(WB).Worksheets(ShCopyTo).Range(RCopyTo), False
    ANmAdvancedFilter = Workbooks(WB).Worksheets(ShCopyTo).Range(RCopyTo).CurrentRegion.Rows.Count
End Function

ShSource, RSource, ShFilter, RFilter, ShCopyTo, Optional RCopyTo = "A1", Optional WB = "This"